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 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
Open the BigQuery page in the Cloud Console.
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
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
Open the BigQuery page in the Cloud Console.
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"
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.