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

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.

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