TABLES view

The INFORMATION_SCHEMA.TABLES view contains one row for each table or view in a dataset. The TABLES and TABLE_OPTIONS views also contain high-level information about views. For detailed information, query the INFORMATION_SCHEMA.VIEWS view.

Required permissions

To query the INFORMATION_SCHEMA.TABLES view, you need the following Identity 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 preceding permissions:

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

For more information about BigQuery permissions, see Access control with IAM.

Schema

When you query the INFORMATION_SCHEMA.TABLES view, the query results contain one row for each table or view in a dataset. For detailed information about views, query the INFORMATION_SCHEMA.VIEWS view instead.

The INFORMATION_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 standard table CLONE: A table clone (Preview) SNAPSHOT: A table snapshot VIEW: A view MATERIALIZED VIEW: A materialized view EXTERNAL: A table that references an external data source
is_insertable_into	STRING	YES or NO depending on whether the table supports DML INSERT statements
is_typed	STRING	The value is always NO
creation_time	TIMESTAMP	The table's creation time
ddl	STRING	The DDL statement that can be used to recreate the table, such as CREATE TABLE or CREATE VIEW
clone_time	TIMESTAMP	For table clones (Preview), the time when the base table was cloned to create this table. If time travel was used, then this field contains the time travel timestamp. Otherwise, the clone_time field is the same as the creation_time field. Applicable only to tables with table_type set to CLONE.
base_table_catalog	STRING	For table clones (Preview), the base table's project. Applicable only to tables with table_type set to CLONE.
base_table_schema	STRING	For table clones (Preview), the base table's dataset. Applicable only to tables with table_type set to CLONE.
base_table_name	STRING	For table clones (Preview), the base table's name. Applicable only to tables with table_type set to CLONE.
default_collation_name	STRING	The name of the default collation specification if it exists; otherwise, NULL.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries 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 more information see Syntax. 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: any dataset region name. For example, region-us.
• DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

Example

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

Examples

Example 1:

The following example retrieves table metadata for all of the tables in the dataset named mydataset. The query selects all of the columns from the INFORMATION_SCHEMA.TABLES view except for is_typed, which is reserved for future use. The metadata that's returned is for all types of tables in mydataset 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 the project 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.

  SELECT
    * EXCEPT(is_typed)
  FROM
    mydataset.INFORMATION_SCHEMA.TABLES;

The result is similar to the following. For readability, some columns are 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 all tables of type BASE TABLE from the INFORMATION_SCHEMA.TABLES view. The is_typed column is excluded. The metadata returned is for tables in mydataset in your default project.

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

  SELECT
    * EXCEPT(is_typed)
  FROM
    mydataset.INFORMATION_SCHEMA.TABLES
  WHERE
    table_type = 'BASE TABLE';

The result is similar to the following. For readability, some columns are 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-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1 |
  |                |               |                |            |                    |                     | (                                           |
  |                |               |                |            |                    |                     |   id INT64                                  |
  |                |               |                |            |                    |                     | );                                          |
  +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
  

Example 3:

The following example retrieves table_name and ddl columns from the INFORMATION_SCHEMA.TABLES view for the population_by_zip_2010 table in the