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