INFORMATION_SCHEMA in BigQuery

INFORMATION_SCHEMA views describe tables, columns, partitions, jobs, storage and more, queried with SQL. Each view is scoped to a dataset or a region, the name is case-sensitive, every query is billed at least 10 MB, and results are never cached.

INFORMATION_SCHEMA is a set of read-only views that describe your BigQuery objects: datasets, tables, columns, partitions, views, routines, jobs, reservations and storage. You query them with ordinary SQL, which makes them the way to answer questions like “which tables have a column called email”, “which partitions changed today” or “who ran the most expensive queries this week”.

Dataset or region

Every view must be qualified. Most table-level views take a dataset, and return metadata for that dataset only:

SELECT table_name, table_type, DATE(creation_time) AS created
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.TABLES`
ORDER BY table_name

Views about the whole project, like JOBS and SCHEMATA, take a region qualifier instead: `region-us`.INFORMATION_SCHEMA.JOBS. A region qualifier covers one location only. region-us is the US multi-region and doesn’t include us-central1 or other single US regions; query each location separately.

The views that accept a dataset qualifier are TABLES, TABLE_OPTIONS, COLUMNS, COLUMN_FIELD_PATHS, PARTITIONS, VIEWS, MATERIALIZED_VIEWS, ROUTINES, ROUTINE_OPTIONS and PARAMETERS. Most of them also accept a region, which then returns that view across every dataset of the project in the region. PARTITIONS and the search index views take a dataset only.

The name is case-sensitive

INFORMATION_SCHEMA and the view name must be in upper case. In lower case, BigQuery reads information_schema as an ordinary dataset name and looks for a dataset of that name:

SELECT table_name
FROM `bigquery-public-data.samples.information_schema.tables`

Columns and nested fields

COLUMNS has one row per top-level column:

SELECT column_name, data_type, is_nullable
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'shakespeare'
ORDER BY ordinal_position

is_nullable is the string 'YES' or 'NO'. data_type is the full type, like ARRAY<STRUCT<sku STRING, qty INT64>>. For the fields inside structs, use COLUMN_FIELD_PATHS, which has one row per field path:

SELECT field_path, data_type
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = 'github_nested' AND field_path LIKE 'repository.%'
ORDER BY field_path
LIMIT 6

To find every table in a dataset that has a column, search COLUMN_FIELD_PATHS by field_path or COLUMNS by column_name:

SELECT table_name, field_path, data_type
FROM mydataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE LOWER(field_path) LIKE '%email%';

The views you will use most

The ddl column of TABLES holds the CREATE TABLE statement that recreates the table, which is the quickest way to copy a schema with its options. In Querylab.io, right-click a table, choose SQL Templates, then CREATE TABLE to get that statement built from the table’s schema, partitioning and clustering (see DDL operations). The table’s Details and Schema tabs show the TABLES and COLUMNS metadata (see Table information):

SELECT ddl
FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'orders';

Querylab.io’s Storage statistics show the logical, physical, long-term and time travel bytes that TABLE_STORAGE holds, for a project, dataset or table, priced under both billing models. For job history and cost analysis, see Monitoring jobs and costs.

What it costs

With on-demand pricing, every INFORMATION_SCHEMA query is billed at least 10 MB, even when it reads less. A dry run already shows that number:

SELECT table_name
FROM `bigquery-public-data.samples.INFORMATION_SCHEMA.TABLES`

Results are never cached, so each run is billed again, even with the same text. That is a fraction of a cent per query, but a dashboard that polls JOBS every minute adds up. Large JOBS queries over long time ranges can read far more than 10 MB; filter on creation_time, the view’s partitioning column.

Permissions

You see metadata only for objects you have access to: a dataset qualifier needs permissions on the dataset, a region qualifier on the project. JOBS_BY_USER shows your own jobs; JOBS for the whole project needs bigquery.jobs.listAll, which comes with the BigQuery Resource Viewer role. TABLE_STORAGE needs bigquery.tables.get and bigquery.tables.list, which the BigQuery Metadata Viewer role has.