Stay organized with collections Save and categorize content based on your preferences.

Introduction to BigQuery INFORMATION_SCHEMA

The BigQuery INFORMATION_SCHEMA views are read-only, system-defined views that provide metadata information about your BigQuery objects. The following table lists all INFORMATION_SCHEMA views that you can query to retrieve metadata information:

Resource type INFORMATION_SCHEMA View
Access control OBJECT_PRIVILEGES
BI Reservations BI_CAPACITIES
BI_CAPACITY_CHANGES
Configuration options EFFECTIVE_PROJECT_OPTIONS
ORGANIZATION_OPTIONS
PROJECT_OPTIONS
Datasets SCHEMATA
SCHEMATA_LINKS
SCHEMATA_OPTIONS
Index metadata SEARCH_INDEXES
SEARCH_INDEXES_COLUMNS
Jobs metadata JOBS_BY_PROJECT
JOBS_BY_USER
JOBS_BY_FOLDER
JOBS_BY_ORGANIZATION
Jobs metadata by timeslice JOBS_TIMELINE_BY_PROJECT
JOBS_TIMELINE_BY_USER
JOBS_TIMELINE_BY_FOLDER
JOBS_TIMELINE_BY_ORGANIZATION
Reservations metadata RESERVATIONS_BY_PROJECT
RESERVATION_CHANGES_BY_PROJECT
RESERVATIONS_TIMELINE_BY_PROJECT
ASSIGNMENTS_BY_PROJECT
ASSIGNMENT_CHANGES_BY_PROJECT
CAPACITY_COMMITMENTS_BY_PROJECT
CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
Routines ROUTINES
Routine options ROUTINE_OPTIONS
Routine parameters PARAMETERS
Session metadata SESSIONS_BY_PROJECT
SESSIONS_BY_USER
Streaming data STREAMING_TIMELINE_BY_PROJECT
STREAMING_TIMELINE_BY_FOLDER
STREAMING_TIMELINE_BY_ORGANIZATION
Tables metadata TABLES
TABLE_OPTIONS
Table columns COLUMNS
COLUMN_FIELD_PATHS
Table partitions PARTITIONS
Table snapshots TABLE_SNAPSHOT
Table storage usage (current) TABLE_STORAGE_BY_PROJECT
TABLE_STORAGE_BY_ORGANIZATION
Table storage usage over time TABLE_STORAGE_TIMELINE_BY_PROJECT
TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
Views VIEWS

For *BY_PROJECT views, the BY_PROJECT suffix is optional. For example, querying INFORMATION_SCHEMA.JOBS_BY_PROJECT and INFORMATION_SCHEMA.JOBS return the same results.

Pricing

For projects that use on-demand pricing, queries against INFORMATION_SCHEMA views incur a minimum of 10 MB of data processing charges, even if the bytes processed by the query are less than 10 MB. 10 MB is the minimum billing amount for on-demand queries. For more information, see On-demand pricing.

For projects that use flat-rate pricing, queries against INFORMATION_SCHEMA views and tables consume your purchased BigQuery slots. For more information, see Flat-rate pricing.

Because INFORMATION_SCHEMA queries are not cached, you are charged each time you run an INFORMATION_SCHEMA query, even if the query text is the same each time you run it.

You are not charged storage fees for the INFORMATION_SCHEMA views.

Syntax

An INFORMATION_SCHEMA view might need to be qualified with a dataset or region.

Dataset qualifier

When present, a dataset qualifier restricts results to the specified dataset. For example:

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;

The following INFORMATION_SCHEMA views support dataset qualifiers:

  • COLUMNS
  • COLUMN_FIELD_PATHS
  • PARAMETERS
  • PARTITIONS
  • ROUTINES
  • ROUTINE_OPTIONS
  • TABLES
  • TABLE_OPTIONS
  • VIEWS

Region qualifier

Region qualifiers are represented using a region-REGION syntax. Any dataset location name can be used for REGION. For example, the following region qualifiers are valid:

  • region-us
  • region-asia-east2
  • region-europe-north1

When present, a region qualifier restricts results to the specified location. For example, the following query returns metadata for all datasets in a project in the US multi-region:

-- Returns metadata for all datasets in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;

The following INFORMATION_SCHEMA views don't support region qualifers:

If neither a region qualifier nor a dataset qualifier is specified, you will receive an error.

Project qualifier

When present, a project qualifier restricts results to the specified project. For example:

-- Returns metadata for the specified project and region.
SELECT * FROM myProject.`region-us`.INFORMATION_SCHEMA.TABLES;

-- Returns metadata for the specified project and dataset.
SELECT * FROM myProject.myDataset.INFORMATION_SCHEMA.TABLES;

All INFORMATION_SCHEMA views support project qualifiers. If a project qualifier is not specified, the view will default to the project in which the query is executing.

Specifying a project qualifier for organization-level views (e.g. STREAMING_TIMELINE_BY_ORGANIZATION, TABLE_STORAGE_TIMELINE_BY_ORGANIZATION) has no impact on the results.

Limitations

  • BigQuery INFORMATION_SCHEMA queries must be in Google Standard SQL syntax. INFORMATION_SCHEMA does not support legacy SQL.
  • INFORMATION_SCHEMA query results are not cached.
  • INFORMATION_SCHEMA views cannot be used in DDL statements.