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 Cloud de Confiance 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"] |
+-------------------+------------------+---------------+----------------------+---------------+------------------+