INFORMATION_SCHEMA is a series of views that provide access to metadata
about datasets, tables, and views.
You can query the INFORMATION_SCHEMA.VIEWS view to retrieve metadata about
views in a dataset.
Required permissions
At a minimum, to get information about views, you must be granted
bigquery.tables.get permissions. The following predefined Cloud IAM
roles include bigquery.tables.get permissions:
bigquery.metadataViewerbigquery.dataViewerbigquery.dataOwnerbigquery.dataEditorbigquery.admin
In addition, if a user has bigquery.datasets.create permissions, when that
user creates a dataset, they are granted bigquery.dataOwner access to it.
bigquery.dataOwner access gives the user the ability to get information about
views in the dataset.
For more information on Cloud IAM roles and permissions in BigQuery, see Access control.
The VIEWS view
When you query the INFORMATION_SCHEMA.VIEWS view, the query results contain
one row for each view in a dataset.
Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset
qualifier. The user submitting the query must have access to the dataset that
contains the views.
The INFORMATION_SCHEMA.VIEWS view has the following schema:
| Column name | Data type | Value |
|---|---|---|
TABLE_CATALOG |
STRING |
The name of the project that contains the dataset |
TABLE_SCHEMA |
STRING |
The name of the dataset that contains the view also referred to as the
dataset id |
TABLE_NAME |
STRING |
The name of the view also referred to as the table id |
VIEW_DEFINITION |
STRING |
The SQL query that defines the view |
CHECK_OPTION |
STRING |
The value returned is always NULL |
USE_STANDARD_SQL |
STRING |
YES if the view was created by using a standard SQL
query; NO if useLegacySql is set to
true |
For more information on table and view properties, see the table resource page in the REST API documentation.
Examples
Example 1:
The following example retrieves all columns from the INFORMATION_SCHEMA.VIEWS
view except for check_option which is reserved for future use. The metadata
returned is for all views in mydataset in your default project —
myproject.
Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset
qualifier. The user submitting the query must have access to the dataset that
contains the views.
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.VIEWS.
To run the query:
Console
Open the BigQuery web UI in the GCP Console.
Enter the following standard SQL query in the Query editor box.
INFORMATION_SCHEMArequires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.SELECT * EXCEPT(check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS
Click Run.
CLI
Use the query command and specify standard SQL syntax by using the
--nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax
is required for INFORMATION_SCHEMA queries.
To run the query, enter:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS'
The results should look like the following:
+----------------+---------------+---------------+---------------------------------------------------------------------+------------------+ | table_catalog | table_schema | table_name | view_definition | use_standard_sql | +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+ | myproject | mydataset | myview | SELECT column1, column2 FROM [myproject:mydataset.mytable] LIMIT 10 | NO | +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
Note that the results show that this view was created by using a legacy SQL query.
Example 2:
The following example retrieves the SQL query and query syntax used to define
myview in mydataset in your default project — myproject.
Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset
qualifier. The user submitting the query must have access to the dataset that
contains the views.
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.VIEWS.
To run the query:
Console
Open the BigQuery web UI in the GCP Console.
Enter the following standard SQL query in the Query editor box.
INFORMATION_SCHEMArequires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name="myview"
Click Run.
CLI
Use the query command and specify standard SQL syntax by using the
--nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax
is required for INFORMATION_SCHEMA queries.
To run the query, enter:
bq query --nouse_legacy_sql \ 'SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name="myview"'
The results should look like the following:
+---------------+---------------------------------------------------------------+------------------+
| table_name | view_definition | use_standard_sql |
+---------------+---------------------------------------------------------------+------------------+
| myview | SELECT column1, column2, column3 FROM mydataset.mytable | YES |
+---------------+---------------------------------------------------------------+------------------+
Note that the results show that this view was created by using a standard SQL query.
Next steps
- To see an overview of
INFORMATION_SCHEMA, go to Introduction to BigQueryINFORMATION_SCHEMA. - Learn how to use
INFORMATION_SCHEMAto get dataset metadata. - Learn how to use
INFORMATION_SCHEMAto get table metadata.
Need help? Visit our