Getting routine metadata using INFORMATION_SCHEMA
INFORMATION_SCHEMA contains these views for routine metadata:
ROUTINESROUTINE_OPTIONSPARAMETERS
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
Required permissions
To get ROUTINES, ROUTINE_OPTIONS, and PARAMETERS views for routine metadata, you need the following 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 granular BigQuery permissions, see Predefined roles and permissions.
Syntax
Queries against any of these views must have a dataset or region qualifier.
-- 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;
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:
|
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
|
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 page in the Google Cloud console.
Enter the following standard SQL query in the Query editor box.
INFORMATION_SCHEMArequires standard SQL syntax. Standard SQL is the default syntax in the Google Cloud console.SELECT * FROM mydataset.INFORMATION_SCHEMA.ROUTINES
Click Run.
bq
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 | 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 | | | | | | | | | | | | | | | | | ); | +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+-----------------------------------------------------------+
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 page in the Google Cloud console.
Enter the following standard SQL query in the Query editor box.
INFORMATION_SCHEMArequires standard SQL syntax. Standard SQL is the default syntax in the Google Cloud console.SELECT * FROM mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
Click Run.
bq
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 page in the Google Cloud console.
Enter the following standard SQL query in the Query editor box.
INFORMATION_SCHEMArequires standard SQL syntax. Standard SQL is the default syntax in the Google Cloud console.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.PARAMETERS WHERE table_type="BASE TABLE"
Click Run.
bq
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 | +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+