Getting routine metadata using INFORMATION_SCHEMA

INFORMATION_SCHEMA contains these views for routine metadata:

ROUTINES
ROUTINE_OPTIONS
PARAMETERS

Required permissions

For ROUTINES, ROUTINE_OPTIONS, and PARAMETERS, you must be granted the bigquery.routines.get and bigquery.routines.list permissions.

`ROUTINES` view

When you query the INFORMATION_SCHEMA.ROUTINES view, the query results contain one row for each routine in a dataset.

The INFORMATION_SCHEMA.ROUTINES view has the following schema:

Column name	Data type	Value
`SPECIFIC_CATALOG`	`STRING`	The name of the project that contains the dataset where the routine is defined
`SPECIFIC_SCHEMA`	`STRING`	The name of the dataset that contains the routine
`SPECIFIC_NAME`	`STRING`	The name of the routine
`ROUTINE_CATALOG`	`STRING`	The name of the project that contains the dataset where the routine is defined
`ROUTINE_SCHEMA`	`STRING`	The name of the dataset that contains the routine
`ROUTINE_NAME`	`STRING`	The name of the routine
`ROUTINE_TYPE`	`STRING`	The routine type: `FUNCTION`: A BigQuery persistent user-defined function `PROCEDURE`: A BigQuery stored procedure
`DATA_TYPE`	`STRING`	The data type that the routine returns. `NULL` if the routine is a stored procedure
`ROUTINE_BODY`	`STRING`	How the body of the routine is defined, either `SQL` or `EXTERNAL` if the routine is a JavaScript user-defined function
`ROUTINE_DEFINITION`	`STRING`	The definition of the routine
`EXTERNAL_LANGUAGE`	`STRING`	`JAVASCRIPT` if the routine is a JavaScript user-defined function or `NULL` if the routine was defined with SQL
`IS_DETERMINISTIC`	`STRING`	`YES` if the routine is known to be deterministic, `NO` if it is not, or `NULL` if unknown
`SECURITY_TYPE`	`STRING`	Security type of the routine, always `NULL`
`CREATED`	`TIMESTAMP`	The routine's creation time
`LAST_MODIFIED`	`TIMESTAMP`	The routine's last modification time

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.ROUTINES view. The metadata returned is for all routines in mydataset in your default project — myproject.

mydataset contains a routine named myroutine1.

To run the query:

Console

Open the BigQuery web UI in the Cloud Console.

Go to the Cloud Console
Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.ROUTINES
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
Click Run.

Command Line

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.ROUTINES'

The results should look like the following:

  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  | specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | data_type | routine_body | routine_definition | external_language | is_deterministic | security_type |           created           |         last_altered        |
  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  | myproject        | mydataset       | myroutine1    | myproject       | mydataset      | myroutine1   | FUNCTION     | NULL      | SQL          | x + 3              | NULL              | NULL             | NULL          | 2019-10-03 17:29:00.235 UTC | 2019-10-03 17:29:00.235 UTC |
  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+

`ROUTINE_OPTIONS` view

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

Examples

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.

To run the query:

Console

Open the BigQuery web UI in the Cloud Console.

Go to the Cloud Console
Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
Click Run.

Command Line

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS'

The results should look like 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"] |
  +-------------------+------------------+---------------+----------------------+---------------+------------------+

`PARAMETERS` view

When you query the INFORMATION_SCHEMA.PARAMETERS view, the query results contain one row for each parameter of each routine in a dataset.

The INFORMATION_SCHEMA.PARAMETERS view has the following schema:

Column name	Data type	Value
`SPECIFIC_CATALOG`	`STRING`	The name of the project that contains the dataset in which the routine containing the parameter is defined
`SPECIFIC_SCHEMA`	`STRING`	The name of the dataset that contains the routine in which the parameter is defined
`SPECIFIC_NAME`	`STRING`	The name of the routine in which the parameter is defined
`ORDINAL_POSITION`	`STRING`	The 1-based position of the parameter, or 0 for the return value
`PARAMETER_MODE`	`STRING`	The mode of the parameter, either `IN`, `OUT`, `INOUT`, or `NULL`
`IS_RESULT`	`STRING`	Whether the parameter is the result of the function, either `YES` or `NO`
`PARAMETER_NAME`	`STRING`	The name of the parameter
`DATA_TYPE`	`STRING`	The type of the parameter, will be `ANY TYPE` if defined as an any type
`PARAMETER_DEFAULT`	`STRING`	The default value of the parameter as a SQL literal value, always `NULL`
`IS_AGGREGATE`	`STRING`	Whether this is an aggregate parameter, always `NULL`

Examples

Example 1:

The following example retrieves all parameters from the INFORMATION_SCHEMA.PARAMETERS view. The metadata returned is for routines in mydataset in your default project — myproject.

To run the query:

Console

Open the BigQuery web UI in the Cloud Console.

Go to the Cloud Console
Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.
```
SELECT
 * EXCEPT(is_typed)
FROM
 mydataset.INFORMATION_SCHEMA.PARAMETERS
WHERE
 table_type="BASE TABLE"
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
Click Run.

Command Line

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.PARAMETERS
 WHERE
   table_type="BASE TABLE"'

The results should look like the following:

  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+
  | specific_catalog  | specific_schema  | specific_name | ordinal_position | parameter_mode | is_result | parameter_name | data_type | parameter_default | is_aggregate |
  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+
  | myproject         | mydataset        | myroutine1    | 0                | NULL           | YES       | NULL           | INT64     | NULL              | NULL         |
  | myproject         | mydataset        | myroutine1    | 1                | NULL           | NO        | x              | INT64     | NULL              | NULL         |
  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+

Was this page helpful? Let us know how we did:

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated November 26, 2019.

Getting routine metadata using INFORMATION_SCHEMA

Required permissions

ROUTINES view

Examples

Example 1:

Console

Command Line

ROUTINE_OPTIONS view

Options table

Examples

Example 1:

Console

Command Line

PARAMETERS view

Examples

Example 1:

Console

Command Line

Send feedback about...

`ROUTINES` view

`ROUTINE_OPTIONS` view

`PARAMETERS` view