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.getbigquery.routines.list
Each of the following predefined IAM roles includes the permissions that you need in order to get routine metadata:
roles/bigquery.adminroles/bigquery.metadataViewerroles/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:
|
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 |
-
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
`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 | | | | | | | | | | | | | | | | | ); | +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+