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

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                                                     |
|                  |                 |               |                 |                |              |              |           |              |                    |                   |                  |               |                             |                             | );                                                        |
+------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+