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

For stability, we recommend that you explicitly list columns in your information schema queries instead of using a wildcard (SELECT *). Explicitly listing columns prevents queries from breaking if the underlying schema changes.

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
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 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
 Replace the following:

  • 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 | +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+