VIEWS view

TheINFORMATION_SCHEMA.VIEWS view contains metadata about views.

Required permissions

To get view metadata, you need the following Identity and Access Management (IAM)permissions:

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

Each of the following predefined IAM roles includes thepermissions 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 BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.VIEWS view, the query results containone row for each view in a dataset.

TheINFORMATION_SCHEMA.VIEWS view has the following schema:

Scope and syntax

Queries against this view must include a dataset or a region qualifier. Forqueries 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 moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

For example:

-- Returns metadata for views in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.VIEWS;-- Returns metadata for all views in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.VIEWS;

Examples

Example 1:

The following example retrieves all columns from theINFORMATION_SCHEMA.VIEWSview except forcheck_option which is reserved for future use. The metadatareturned is for all views inmydataset in your default project —myproject.

To run the query against a project other than your default project, add theproject ID to the dataset in the following format:`project_id`.dataset.INFORMATION_SCHEMA.view;for example,`myproject`.mydataset.INFORMATION_SCHEMA.VIEWS.

SELECT*EXCEPT(check_option)FROMmydataset.INFORMATION_SCHEMA.VIEWS;

  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+  | table_catalog  | table_schema  |  table_name   |                        view_definition                              | use_standard_sql |  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+  | myproject      | mydataset     | myview        | SELECT column1, column2 FROM [myproject:mydataset.mytable] LIMIT 10 | NO               |  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+

SELECTtable_name,view_definition,use_standard_sqlFROMmydataset.INFORMATION_SCHEMA.VIEWSWHEREtable_name='myview';

  +---------------+---------------------------------------------------------------+------------------+  |  table_name   |                        view_definition                        | use_standard_sql |  +---------------+---------------------------------------------------------------+------------------+  | myview        | SELECT column1, column2, column3 FROMmydataset.mytable       | YES              |  +---------------+---------------------------------------------------------------+------------------+