INFORMATION_SCHEMA: explore a dataset

Every dataset can describe itself. Before you query a table you do not know, ask BigQuery with plain SQL what tables exist and what their columns are.

How it works

INFORMATION_SCHEMA is a set of read-only views that describe your data: one row for each table, for each column, for each field. You query them like tables. Name the dataset before the view: write learn.mot in backticks, a dot, and then INFORMATION_SCHEMA.COLUMNS.

Common mistake

The view names are case sensitive. Write INFORMATION_SCHEMA in capitals and the view as BigQuery spells it. Any other spelling fails.

Worked example

SELECT column_name, data_type
FROM `learn.mot`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'dim_vehicle'
ORDER BY ordinal_position
LIMIT 4

The query lists the first four columns of dim_vehicle with their types.

Nothing is read from dim_vehicle itself, only its description.

All BigQuery lessons