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
To get information about views, you must be assigned the
READER role on the
dataset, or you must be assigned a project-level IAM role that
includes bigquery.tables.get permissions. If you are granted
bigquery.tables.get permissions at the project level, you can get information
about all views in the project. All predefined, project-level IAM roles include
bigquery.tables.get permissions except for bigquery.jobUser and
bigquery.user.
In addition, a user assigned the bigquery.user role has
bigquery.datasets.create permissions. This allows a user assigned to the
bigquery.user role to get information about views in any dataset that user
creates. When a user assigned to the bigquery.user role creates a dataset,
that user is given OWNER access to the dataset. OWNER access to a dataset
gives the user full control over it and all the views in it.
For more information on IAM roles and permissions in BigQuery, see Access Control. For more information on dataset-level roles, see Primitive roles for datasets.
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.
Command-line
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.
Command-line
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