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

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 .

Note: You must use a region qualifier to query INFORMATION_SCHEMA views. The location of the query execution must match the region of the INFORMATION_SCHEMA view.

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

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