ROUTINES view

The INFORMATION_SCHEMA.ROUTINES view contains one row for each routine in a dataset.

Required permissions

To query the INFORMATION_SCHEMA.ROUTINES 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.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
  • TABLE FUNCTION: A BigQuery table function.
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_ALTERED TIMESTAMP The routine's last modification time
DDL STRING The DDL statement that can be used to create the routine, such as CREATE FUNCTION or CREATE PROCEDURE
CONNECTION STRING The connection name, if the routine has one. Otherwise NULL

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.ROUTINES Project level REGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.ROUTINES 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.ROUTINES;  -- Returns metadata for routines in a region. SELECT * FROM region-us.INFORMATION_SCHEMA.ROUTINES;

Example

Example

To run the query against a project other than your default project, add the project ID to the dataset in the following format: 

`PROJECT_ID`.INFORMATION_SCHEMA.ROUTINES
. For example, `myproject`.INFORMATION_SCHEMA.ROUTINES.

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. The dataset mydataset contains a routine named myroutine1.

SELECT   * FROM   mydataset.INFORMATION_SCHEMA.ROUTINES;

The result is similar to 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        |                            ddl                             | +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+ | 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 | CREATE FUNCTION myproject.mydataset.myroutine1(x FLOAT64) | |                  |                 |               |                 |                |              |              |           |              |                    |                   |                  |               |                             |                             | AS (                                                      | |                  |                 |               |                 |                |              |              |           |              |                    |                   |                  |               |                             |                             | x + 3                                                     | |                  |                 |               |                 |                |              |              |           |              |                    |                   |                  |               |                             |                             | );                                                        | +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+