PARAMETERS view
The INFORMATION_SCHEMA.PARAMETERS
view contains one row for each parameter of
each routine in a dataset.
Required permissions
To query the INFORMATION_SCHEMA.PARAMETERS
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 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.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 |
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.PARAMETERS |
Project level | REGION |
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.PARAMETERS |
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 parameters of a routine in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.PARAMETERS;
-- Returns metadata for parameters of a routine in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.PARAMETERS;
Example
Example
To run the query against a dataset in a project other than your default project, add the project ID in the following format:
`PROJECT_ID`.`DATASET_ID`.INFORMATION_SCHEMA.PARAMETERS
PROJECT_ID
: the ID of the project.DATASET_ID
: the ID of the dataset.
For example, example-project.mydataset.INFORMATION_SCHEMA.JOBS_BY_PROJECT
.
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
.
SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.PARAMETERS WHERE table_type = 'BASE TABLE';
The result is similar to 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 | +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+