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

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.

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"] |
+-------------------+------------------+---------------+----------------------+---------------+------------------+