Getting view metadata using INFORMATION_SCHEMA

INFORMATION_SCHEMA.VIEWS contains metadata about views.

Required permissions

For VIEWS, you must be granted the bigquery.tables.get and bigquery.tables.list permissions.

Syntax

Queries against this view must have a dataset qualifier.

-- Returns metadata for views in a single dataset.
SELECT * FROM myDataset.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 web UI in the Cloud Console.

    Go to the Cloud Console

  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

  3. 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.

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 web UI in the Cloud Console.

    Go to the Cloud Console

  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"

  3. 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.