VIEWS view

The INFORMATION_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 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 BigQuery permissions, see Access control with IAM .

Schema

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 GoogleSQL query; `NO` if `useLegacySql` is set to `true`

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries 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 more information see Syntax . The following table explains the region and resource scopes for this view:

View name	Resource scope	Region scope
[ PROJECT_ID .]`region- REGION `.INFORMATION_SCHEMA.VIEWS	Project level	`REGION`
`[ PROJECT_ID .] DATASET_ID .INFORMATION_SCHEMA.VIEWS`	Dataset level	Dataset location

Replace the following:

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

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

For example:

  -- 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 
 ;

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 .

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

The result is similar to 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 .

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

The result is similar to 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 GoogleSQL query.