TABLES view

TheINFORMATION_SCHEMA.TABLES view contains one row for each table or view ina dataset. TheTABLES andTABLE_OPTIONS views also contain high-level information about views.For detailed information, query theINFORMATION_SCHEMA.VIEWS view.

Required permissions

To query theINFORMATION_SCHEMA.TABLES view, you need the followingIdentity and Access Management (IAM) permissions:

bigquery.tables.get
bigquery.tables.list
bigquery.routines.get
bigquery.routines.list

Each of the following predefined IAM roles includes the precedingpermissions:

roles/bigquery.admin
roles/bigquery.dataViewer
roles/bigquery.metadataViewer

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.TABLES view, the query results containone row for each table or view in a dataset. For detailed information aboutviews, query theINFORMATION_SCHEMA.VIEWSview instead.

TheINFORMATION_SCHEMA.TABLES view has the following schema:

Column name	Data type	Value
`table_catalog`	`STRING`	The project ID of the project that contains the dataset.
`table_schema`	`STRING`	The name of the dataset that contains the table or view. Also referred to as the`datasetId`.
`table_name`	`STRING`	The name of the table or view. Also referred to as the`tableId`.
`table_type`	`STRING`	The table type; one of the following: `BASE TABLE`: A standardtable `CLONE`: Atable clone `SNAPSHOT`: Atable snapshot `VIEW`: Aview `MATERIALIZED VIEW`: Amaterialized view ormaterialized view replica `EXTERNAL`: A table that references anexternal data source
`managed_table_type`	`STRING`	This column is in Preview. The managed table type; one of the following: `NATIVE`: A standardtable `BIGLAKE`: A BigLake table for Apache Iceberg in BigQuery
`is_insertable_into`	`STRING`	`YES` or`NO` depending on whether the table supportsDML INSERT statements
`is_fine_grained_mutations_enabled`	`STRING`	`YES` or`NO` depending on whetherfine-grained DML mutations are enabled on the table
`is_typed`	`STRING`	The value is always`NO`
`is_change_history_enabled`	`STRING`	`YES` or`NO` depending on whetherchange history is enabled
`creation_time`	`TIMESTAMP`	The table's creation time
`base_table_catalog`	`STRING`	Fortable clones andtable snapshots, the base table's project. Applicable only to tables with`table_type` set to`CLONE` or`SNAPSHOT`.
`base_table_schema`	`STRING`	Fortable clones andtable snapshots, the base table's dataset. Applicable only to tables with`table_type` set to`CLONE` or`SNAPSHOT`.
`base_table_name`	`STRING`	Fortable clones andtable snapshots, the base table's name. Applicable only to tables with`table_type` set to`CLONE` or`SNAPSHOT`.
`snapshot_time_ms`	`TIMESTAMP`	Fortable clones andtable snapshots, the time when theclone orsnapshot operation was run on the base table to create this table. Iftime travel was used, then this field contains the time travel timestamp. Otherwise, the`snapshot_time_ms` field is the same as the`creation_time` field. Applicable only to tables with`table_type` set to`CLONE` or`SNAPSHOT`.
`replica_source_catalog`	`STRING`	Formaterialized view replicas, the base materialized view's project.
`replica_source_schema`	`STRING`	Formaterialized view replicas, the base materialized view's dataset.
`replica_source_name`	`STRING`	Formaterialized view replicas, the base materialized view's name.
`replication_status`	`STRING`	Formaterialized view replicas, the status of the replication from the base materialized view to the materialized view replica; one of the following: `REPLICATION_STATUS_UNSPECIFIED` `ACTIVE`: Replication is active with no errors `SOURCE_DELETED`: The source materialized view has been deleted `PERMISSION_DENIED`: The source materialized view hasn't beenauthorized on the dataset that contains the source Amazon S3 BigLake tables used in the query that created the materialized view. `UNSUPPORTED_CONFIGURATION`: There is an issue with the replica'sprerequisites other than source materialized view authorization.
`replication_error`	`STRING`	If`replication_status` indicates a replication issue for amaterialized view replica,`replication_error` provides further details about the issue.
`ddl`	`STRING`	TheDDL statement that can be used to recreate the table, such as`CREATE TABLE` or`CREATE VIEW`
`default_collation_name`	`STRING`	The name of the defaultcollation specification if it exists; otherwise,`NULL`.
`sync_status`	`JSON`	The status of the sync between the primary and secondary replicas forcross-region replication anddisaster recovery datasets. Returns`NULL` if the replica is a primary replica or the dataset doesn't use replication.
`upsert_stream_apply_watermark`	`TIMESTAMP`	For tables that use change data capture (CDC), the time when row modifications were last applied. For more information, seeMonitor table upsert operation progress.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. Forqueries with a dataset qualifier, you must have permissions for the dataset.For queries with a region qualifier, you must have permissions for the project.For moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.TABLES	Project level	`REGION`
`[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.TABLES`	Dataset level	Dataset location

Replace the following:

Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
REGION: anydataset region name. For example,`region-us`.
DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.
Note: You must use a region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

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

Examples

Example 1:

The following example retrieves table metadata for all of the tables in thedataset namedmydataset. The metadata that'sreturned is for all types of tables inmydataset in your default project.

mydataset contains the following tables:

mytable1: a standard BigQuery table
myview1: a BigQuery view

To run the query against a project other than your default project, add theproject ID to the dataset in the following format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`myproject`.mydataset.INFORMATION_SCHEMA.TABLES.

Note:INFORMATION_SCHEMA view names are case-sensitive.

SELECTtable_catalog,table_schema,table_name,table_type,is_insertable_into,creation_time,ddlFROMmydataset.INFORMATION_SCHEMA.TABLES;

The result is similar to the following. For readability, some columnsare excluded from the result.

+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+| table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |                     ddl                     |+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+| myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` ||                |               |                |            |                    |                     | (                                           ||                |               |                |            |                    |                     |   id INT64                                  ||                |               |                |            |                    |                     | );                                          || myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1`   ||                |               |                |            |                    |                     | AS SELECT 100 as id;                        |+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+

Example 2:

The following example retrieves table metadata for all tables of typeCLONEorSNAPSHOT from theINFORMATION_SCHEMA.TABLES view. The metadata returnedis for tables inmydataset in your default project.

SELECTtable_name,table_type,base_table_catalog,base_table_schema,base_table_name,snapshot_time_msFROMmydataset.INFORMATION_SCHEMA.TABLESWHEREtable_type='CLONE'ORtable_type='SNAPSHOT';

The result is similar to the following. For readability, some columnsare excluded from the result.

  +--------------+------------+--------------------+-------------------+-----------------+---------------------+  | table_name   | table_type | base_table_catalog | base_table_schema | base_table_name | snapshot_time_ms    |  +--------------+------------+--------------------+-------------------+-----------------+---------------------+  | items_clone  | CLONE      | myproject          | mydataset         | items           | 2018-10-31 22:40:05 |  | orders_bk    | SNAPSHOT   | myproject          | mydataset         | orders          | 2018-11-01 08:22:39 |  +--------------+------------+--------------------+-------------------+-----------------+---------------------+

Example 3:

The following example retrievestable_name andddl columns from theINFORMATION_SCHEMA.TABLESview for thepopulation_by_zip_2010 table in the census_bureau_usadataset. This dataset is part of the BigQuerypublic dataset program.

Because the table you're querying is in another project, you add the project ID to the dataset inthe following format:`project_id`.dataset.INFORMATION_SCHEMA.view.In this example, the value is`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.

SELECTtable_name,ddlFROM`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLESWHEREtable_name='population_by_zip_2010';

The result is similar to the following:

+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|       table_name       |                                                                                                            ddl                                                                                                             |+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010`                                                                                                                                               ||                        | (                                                                                                                                                                                                                          ||                        |   geo_id STRING OPTIONS(description="Geo code"),                                                                                                                                                                           ||                        |   zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"),                                                                                                                          ||                        |   population INT64 OPTIONS(description="The total count of the population for this segment."),                                                                                                                             ||                        |   minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."),                                                          ||                        |   maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), ||                        |   gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.")                                                                                                                    ||                        | )                                                                                                                                                                                                                          ||                        | OPTIONS(                                                                                                                                                                                                                   ||                        |   labels=[("freebqcovid", "")]                                                                                                                                                                                             ||                        | );                                                                                                                                                                                                                         |+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.

Movatterモバイル変換