Getting metadata about views using INFORMATION_SCHEMA

INFORMATION_SCHEMA.VIEWS contains metadata about views.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

Required permissions

To get view metadata, you need the following IAM permissions:

• bigquery.tables.get
• bigquery.tables.list

Each of the following predefined IAM roles includes the permissions that you need in order to get view metadata:

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

For more information about granular BigQuery permissions, see roles and permissions.

Syntax

Queries against this view must have a dataset or region qualifier.

-- Returns metadata for views in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.VIEWS;

-- Returns metadata for all views in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.VIEWS;

The VIEWS view

When you query the INFORMATION_SCHEMA.VIEWS view, the query results contain one row for each view in a dataset.

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

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.

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

1. Open the BigQuery page in the Cloud Console.

   Go to the BigQuery page

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

   SELECT
    * EXCEPT(check_option)
   FROM
    mydataset.INFORMATION_SCHEMA.VIEWS
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

bq

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.

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

1. Open the BigQuery page in the Cloud Console.

   Go to the BigQuery page

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

   SELECT
    table_name, view_definition, use_standard_sql
   FROM
    mydataset.INFORMATION_SCHEMA.VIEWS
   WHERE
    table_name="myview"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

bq

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.