ROUTINE_OPTIONS view
The INFORMATION_SCHEMA.ROUTINE_OPTIONS
view contains one row for each option
of each routine in a dataset.
Required permissions
To query the INFORMATION_SCHEMA.ROUTINE_OPTIONS
view, you need the following
Identity and Access Management (IAM) permissions:
bigquery.routines.get
bigquery.routines.list
Each of the following predefined IAM roles includes the permissions that you need in order to get routine metadata:
roles/bigquery.admin
roles/bigquery.metadataViewer
roles/bigquery.dataViewer
For more information about BigQuery permissions, see Access control with IAM.
Schema
When you query the INFORMATION_SCHEMA.ROUTINE_OPTIONS
view, the query results
contain one row for each option of each routine in a dataset.
The INFORMATION_SCHEMA.ROUTINE_OPTIONS
view has the following schema:
Column name | Data type | Value |
---|---|---|
SPECIFIC_CATALOG |
STRING |
The name of the project that contains the routine where the option is defined |
SPECIFIC_SCHEMA |
STRING |
The name of the dataset that contains the routine where the option is defined |
SPECIFIC_NAME |
STRING |
The name of the routine |
OPTION_NAME |
STRING |
One of the name values in the options table |
OPTION_TYPE |
STRING |
One of the data type values in the options table |
OPTION_VALUE |
STRING |
One of the value options in the options table |
Options table
OPTION_NAME |
OPTION_TYPE |
OPTION_VALUE |
---|---|---|
description |
STRING |
The description of the routine, if defined |
library |
ARRAY |
The names of the libraries referenced in the routine. Only applicable to JavaScript UDFs |
data_governance_type |
DataGovernanceType |
The name of supported data governance type. For example,
DATA_MASKING . |
Scope and syntax
Queries against this view must include a dataset or a region qualifier. 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.ROUTINE_OPTIONS |
Project level | REGION |
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.ROUTINE_OPTIONS |
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.
Example
-- Returns metadata for routines in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;
-- Returns metadata for routines in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.ROUTINE_OPTIONS;
Example
Example 1:
The following example retrieves the routine options for all
routines in mydataset
in your default project (myproject
) by querying the
INFORMATION_SCHEMA.ROUTINE_OPTIONS
view:
SELECT * FROM mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;
The result is similar to the following:
+-------------------+------------------+---------------+----------------------+---------------+------------------+ | specific_catalog | specific_schema | specific_name | option_name | option_type | option_value | +-------------------+------------------+---------------+----------------------+---------------+------------------+ | myproject | mydataset | myroutine1 | description | STRING | "a description" | | myproject | mydataset | myroutine2 | library | ARRAY<STRING> | ["a.js", "b.js"] | +-------------------+------------------+---------------+----------------------+---------------+------------------+