Introduction to 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 Engine BI_CAPACITIES
BI_CAPACITY_CHANGES
Configurations EFFECTIVE_PROJECT_OPTIONS
ORGANIZATION_OPTIONS
ORGANIZATION_OPTIONS_CHANGES
PROJECT_OPTIONS
PROJECT_OPTIONS_CHANGES
Datasets SCHEMATA
SCHEMATA_LINKS
SCHEMATA_OPTIONS
SHARED_DATASET_USAGE
SCHEMATA_REPLICAS
Jobs JOBS_BY_PROJECT
JOBS_BY_USER
JOBS_BY_FOLDER
JOBS_BY_ORGANIZATION
Jobs by timeslice JOBS_TIMELINE_BY_PROJECT
JOBS_TIMELINE_BY_USER
JOBS_TIMELINE_BY_FOLDER
JOBS_TIMELINE_BY_ORGANIZATION
Recommendations and insights INSIGHTS
RECOMMENDATIONS
RECOMMENDATIONS_BY_ORGANIZATION
Reservations ASSIGNMENTS_BY_PROJECT
ASSIGNMENT_CHANGES_BY_PROJECT
CAPACITY_COMMITMENTS_BY_PROJECT
CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
RESERVATIONS_BY_PROJECT
RESERVATION_CHANGES_BY_PROJECT
RESERVATIONS_TIMELINE_BY_PROJECT
Routines PARAMETERS
ROUTINES
ROUTINE_OPTIONS
Search indexes SEARCH_INDEXES
SEARCH_INDEX_COLUMNS
Sessions SESSIONS_BY_PROJECT
SESSIONS_BY_USER
Streaming STREAMING_TIMELINE_BY_PROJECT
STREAMING_TIMELINE_BY_FOLDER
STREAMING_TIMELINE_BY_ORGANIZATION
Tables COLUMNS
COLUMN_FIELD_PATHS
CONSTRAINT_COLUMN_USAGE
KEY_COLUMN_USAGE
PARTITIONS
TABLES
TABLE_OPTIONS
TABLE_CONSTRAINTS
TABLE_SNAPSHOTS
TABLE_STORAGE_BY_PROJECT
TABLE_STORAGE_BY_ORGANIZATION
TABLE_STORAGE_USAGE_TIMELINE
TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION
Vector indexes VECTOR_INDEXES
VECTOR_INDEX_COLUMNS
VECTOR_INDEX_OPTIONS
Views VIEWS
MATERIALIZED_VIEWS
Write API WRITE_API_TIMELINE_BY_PROJECT
WRITE_API_TIMELINE_BY_FOLDER
WRITE_API_TIMELINE_BY_ORGANIZATION

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 capacity-based pricing, queries against INFORMATION_SCHEMA views and tables consume your purchased BigQuery slots. For more information, see capacity-based pricing.

Because INFORMATION_SCHEMA queries are not cached, you are charged each time that 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 needs 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
  • MATERIALIZED_VIEWS
  • 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 qualifiers:

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

Queries against a region-qualified INFORMATION_SCHEMA view run in the region that you specify, which means that you can't write a single query to join data from views in different regions. To combine INFORMATION_SCHEMA views from multiple regions, write your query results to tables and then copy your tables or move your datasets to a common region.

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) has no impact on the results.

Limitations

  • BigQuery INFORMATION_SCHEMA queries must be in GoogleSQL syntax. INFORMATION_SCHEMA does not support legacy SQL.
  • INFORMATION_SCHEMA query results are not cached.
  • INFORMATION_SCHEMA views cannot be used in DDL statements.
  • INFORMATION_SCHEMA views don't contain information about hidden datasets.
  • INFORMATION_SCHEMA queries with region qualifiers might include metadata from resources in that region from deleted datasets that are within your time travel window.
  • When you list resources from an INFORMATION_SCHEMA view, the permissions are checked only at the parent level, not at an individual row level. Therefore, any deny policy (preview) that conditionally targets an individual row using tags is ignored.