How can I see list of columns and tables in a dataset ?

[VinSin]

Hi All,

I want to query the metadata of a dataset..

'ex: list of all the columns , tables views etc.

I have found a table called __TABLES_SUMMARY__ , I want to get column info too.

Thanks & regards,

vinay

[ms4446]

In BigQuery, you can query the metadata of datasets, tables, and columns using the INFORMATION_SCHEMA views. These views offer comprehensive information about your BigQuery resources.

Listing Tables and Views:

SELECT
  table_name,
  table_type
FROM
  `your-project-id.your-dataset-id.INFORMATION_SCHEMA.TABLES`
ORDER BY
  table_name;

• Replace your-project-id and your-dataset-id with your actual values.
• This query provides the table/view name and its type (e.g., TABLE, VIEW, MATERIALIZED VIEW).

Listing Columns:

SELECT
  table_name,
  column_name,
  data_type,
  is_nullable
FROM
  `your-project-id.your-dataset-id.INFORMATION_SCHEMA.COLUMNS`
ORDER BY
  table_name,
  ordinal_position;  -- Ensures columns are listed in their original order within tables

• This query gives you the table name, column name, data type, and nullability for each column.
• The ordinal_position ensures columns are listed in their correct order within their respective tables.

Combining Table and Column Information:

SELECT
  t.table_name,
  t.table_type,
  c.column_name,
  c.data_type,
  c.is_nullable
FROM
  `your-project-id.your-dataset-id.INFORMATION_SCHEMA.TABLES` t
LEFT JOIN   -- Use LEFT JOIN to include tables without columns
  `your-project-id.your-dataset-id.INFORMATION_SCHEMA.COLUMNS` c
ON
  t.table_name = c.table_name
ORDER BY
  t.table_name,
  c.ordinal_position;

• A LEFT JOIN is used to include tables that might not have any columns.
• The query provides a combined view of tables/views along with their column details.

Additional Considerations:

• Permissions: Ensure you have the BigQuery Data Viewer or BigQuery Metadata Viewer role on the dataset to query these views.
• Beta: The INFORMATION_SCHEMA views are still in beta, so their structure might evolve. Check the official documentation for the latest details.
• Performance: Querying these views incurs costs. Be mindful of the amount of data you're querying, especially for large datasets.