Introduction to INFORMATION_SCHEMA

The BigQueryINFORMATION_SCHEMA views are read-only, system-definedviews that provide metadata information about your BigQueryobjects. The following table lists allINFORMATION_SCHEMA views that you canquery to retrieve metadata information:

^† For*BY_PROJECT views, theBY_PROJECT suffix is optional. Forexample, queryingINFORMATION_SCHEMA.JOBS_BY_PROJECT andINFORMATION_SCHEMA.JOBSreturn the same results.

Pricing

For projects that use on-demand pricing, queries againstINFORMATION_SCHEMAviews incur a minimum of 10 MB of data processing charges, even if the bytesprocessed by the query are less than 10 MB. 10 MB is the minimumbilling amount for on-demand queries. For more information, seeOn-demand pricing.

For projects that use capacity-based pricing, queries againstINFORMATION_SCHEMAviews and tables consume your purchased BigQuery slots. For moreinformation, seecapacity-based pricing.

BecauseINFORMATION_SCHEMA queries are not cached, you are charged each timethat you run anINFORMATION_SCHEMA query, even if the query text is the sameeach time you run it.

You are not charged storage fees for theINFORMATION_SCHEMA views.

Syntax

AnINFORMATION_SCHEMA view needs to be qualified with a dataset or region.

Table myproject: region-us.INFORMATION_SCHEMA.[VIEW] not found in location US

Dataset qualifier

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

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

The followingINFORMATION_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 aregion-REGION syntax.Anydataset location name can be used forREGION. For example, the following region qualifiersare valid:

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

When present, a region qualifier restricts results to the specified location.Region qualifiers aren'thierarchical, which means the EU multi-region does not includeeurope-*regions nor does the US multi-region include theus-* regions. For example,the following query returns metadata for all datasets in theUS multi-regionfor the project in which the query is executing, but doesn't include datasets intheus-west1 region:

-- Returns metadata for all datasets in the US multi-region.SELECT*FROMregion-us.INFORMATION_SCHEMA.SCHEMATA;

The followingINFORMATION_SCHEMA views don't support region qualifiers:

• INFORMATION_SCHEMA.PARTITIONS
• INFORMATION_SCHEMA.SEARCH_INDEXES
• INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS
• INFORMATION_SCHEMA.SEARCH_INDEX_OPTIONS

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

Queries against a region-qualifiedINFORMATION_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 combineINFORMATION_SCHEMA views from multiple regions, read and combine the query results locally, orcopy the resulting tables 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*FROMmyProject.`region-us`.INFORMATION_SCHEMA.TABLES;-- Returns metadata for the specified project and dataset.SELECT*FROMmyProject.myDataset.INFORMATION_SCHEMA.TABLES;

AllINFORMATION_SCHEMA views support project qualifiers. If a projectqualifier is not specified, the view will default to theproject 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

• BigQueryINFORMATION_SCHEMA queries must be in GoogleSQLsyntax.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 abouthidden datasets.
• INFORMATION_SCHEMA queries with region qualifiers might include metadatafrom resources in that region fromdeleted datasets that are within your time travel window.
• When you list resources from anINFORMATION_SCHEMA view, the permissions arechecked only at the parent level, not at an individual row level. Therefore,anydeny policy(preview) that conditionally targets anindividual row using tags is ignored.