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.getbigquery.tables.listbigquery.routines.getbigquery.routines.list
Each of the following predefined IAM roles includes the preceding permissions:
roles/bigquery.adminroles/bigquery.dataViewerroles/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:
|
is_insertable_into |
STRING |
YES or NO depending on whether the table
supports DML INSERT
statements |
is_typed |
STRING |
The value is always NO |
is_change_history_enabled |
STRING |
YES or NO depending on whether
change history
is enabled |
creation_time |
TIMESTAMP |
The table's creation time |
base_table_catalog |
STRING |
For table clones
and table snapshots,
the base table's project. Applicable only to
tables with table_type set to CLONE or
SNAPSHOT.
|
base_table_schema |
STRING |
For table clones
and table snapshots,
the base table's dataset. Applicable only to tables with
table_type set to CLONE or
SNAPSHOT. |
base_table_name |
STRING |
For table clones
and table snapshots,
the base table's name. Applicable only to tables with
table_type set to CLONE or
SNAPSHOT. |
snapshot_time_ms |
TIMESTAMP |
For table clones
and table snapshots,
the time when the clone
or snapshot
operation was run on the base table to create this table. If
time 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 |
For materialized view replicas, the base materialized view's project. |
replica_source_schema |
STRING |
For materialized view replicas, the base materialized view's dataset. |
replica_source_name |
STRING |
For materialized view replicas, the base materialized view's name. |
replication_status |
STRING |
For
materialized view replicas,
the status of the replication from the base materialized view to the
materialized view replica; one of the following:
|
replication_error |
STRING |
If replication_status indicates a replication issue for a
materialized view replica,
replication_error provides further details about the issue. |
ddl |
STRING |
The DDL statement
that can be used to recreate the table, such as
CREATE TABLE
or CREATE VIEW |
default_collation_name |
STRING |
The name of the default collation specification
if it exists; otherwise, NULL.
|
upsert_stream_apply_watermark |
TIMESTAMP |
For tables that use change data capture (CDC), the time when row modifications were last applied. For more information, see Monitor table upsert operation progress. |
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 |
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 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 tablemyview1: 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.
SELECT table_catalog, table_schema, table_name, table_type, is_insertable_into, creation_time, ddl 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 table metadata for all tables of type CLONE
or SNAPSHOT from the INFORMATION_SCHEMA.TABLES view. 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
table_name, table_type, base_table_catalog,
base_table_schema, base_table_name, snapshot_time_ms
FROM
mydataset.INFORMATION_SCHEMA.TABLES
WHERE
table_type = 'CLONE'
OR
table_type = 'SNAPSHOT';
The result is similar to the following. For readability, some columns are 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 retrieves table_name and ddl columns from the INFORMATION_SCHEMA.TABLES
view for the population_by_zip_2010 table in the
census_bureau_usa
dataset. This dataset is part of the BigQuery
public dataset program.
Because the table you're querying is in another project, you add the project ID to the dataset in
the following format:
`project_id`.dataset.INFORMATION_SCHEMA.view.
In this example, the value is
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES.
SELECT table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_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", "")] |
| | ); |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+