ROUTINES view

TheINFORMATION_SCHEMA.ROUTINES view contains one row for each routine in adataset.

Required permissions

To query theINFORMATION_SCHEMA.ROUTINES 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.ROUTINES view, the query results containone row for each routine in a dataset.

TheINFORMATION_SCHEMA.ROUTINES view has the following schema:

Column nameData typeValue
specific_catalogSTRINGThe name of the project that contains the dataset where the routine is defined
specific_schemaSTRINGThe name of the dataset that contains the routine
specific_nameSTRINGThe name of the routine
routine_catalogSTRINGThe name of the project that contains the dataset where the routine is defined
routine_schemaSTRINGThe name of the dataset that contains the routine
routine_nameSTRINGThe name of the routine
routine_typeSTRINGThe routine type:
  • FUNCTION: A BigQuery persistent user-defined function
  • AGGREGATE FUNCTION: A BigQuery persistent user-defined aggregate function
  • PROCEDURE: A BigQuery stored procedure
  • TABLE FUNCTION: A BigQuery table function
data_typeSTRINGThe data type that the routine returns.NULL if the routine is a stored procedure
routine_bodySTRINGHow the body of the routine is defined, eitherSQL orEXTERNAL if the routine is a JavaScript user-defined function
routine_definitionSTRINGThe definition of the routine
external_languageSTRINGJAVASCRIPT if the routine is a JavaScript user-defined function orNULL if the routine was defined with SQL
is_deterministicSTRINGYES if the routine is known to be deterministic,NO if it is not, orNULL if unknown
security_typeSTRINGSecurity type of the routine, alwaysNULL
createdTIMESTAMPThe routine's creation time
last_alteredTIMESTAMPThe routine's last modification time
ddlSTRINGTheDDL statement that can be used to create the routine, such asCREATE FUNCTION orCREATE PROCEDURE
connectionSTRINGThe connection name, if the routine has one. OtherwiseNULL

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:

View nameResource scopeRegion scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.ROUTINESProject levelREGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.ROUTINESDataset levelDataset location
Replace the following:
  • 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.ROUTINES;-- Returns metadata for routines in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.ROUTINES;

Example

Example

To run the query against a project other than your default project, add theproject 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 theINFORMATION_SCHEMA.ROUTINES view. The metadata returned is for all routines inmydataset in your default project —myproject. The datasetmydatasetcontains a routine namedmyroutine1.

SELECT*FROMmydataset.INFORMATION_SCHEMA.ROUTINES;
Note:INFORMATION_SCHEMA view names are case-sensitive.

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

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

Last updated 2026-02-19 UTC.