ROUTINE_OPTIONS view

TheINFORMATION_SCHEMA.ROUTINE_OPTIONS view contains one row for each optionof each routine in a dataset.

Required permissions

To query theINFORMATION_SCHEMA.ROUTINE_OPTIONS view, you need the followingIdentity and Access Management (IAM) permissions:

• bigquery.routines.get
• bigquery.routines.list

Each of the following predefined IAM roles includes thepermissions that you need in order to get routine metadata:

• roles/bigquery.admin
• roles/bigquery.metadataViewer
• roles/bigquery.dataViewer

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.ROUTINE_OPTIONS view, the query resultscontain one row for each option of each routine in a dataset.

TheINFORMATION_SCHEMA.ROUTINE_OPTIONS view has the following schema:

Options table

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

-- Returns metadata for routines in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;-- Returns metadata for routines in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.ROUTINE_OPTIONS;

Example

Example 1:

The following example retrieves the routine options for allroutines inmydataset in your default project (myproject) by querying theINFORMATION_SCHEMA.ROUTINE_OPTIONS view:

SELECT*FROMmydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS;

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