PARAMETERS view

TheINFORMATION_SCHEMA.PARAMETERS view contains one row for each parameter ofeach routine in a dataset.

Required permissions

To query theINFORMATION_SCHEMA.PARAMETERS view, you need the followingIdentity and Access Management (IAM) permissions:

• bigquery.routines.get
• bigquery.routines.list

Each of the following predefined IAM roles includes thepermissions that you need to get routine metadata:

• roles/bigquery.admin
• roles/bigquery.metadataViewer
• roles/bigquery.dataViewer

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.PARAMETERS view, the query resultscontain one row for each parameter of each routine in a dataset.

TheINFORMATION_SCHEMA.PARAMETERS view has the following schema:

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

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For moreinformation seeSyntax.The following table explains the region and resource scopes for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

-- Returns metadata for parameters of a routine in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.PARAMETERS;-- Returns metadata for parameters of a routine in a region.SELECT*FROMregion-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 theINFORMATION_SCHEMA.PARAMETERS view. The metadata returned is for routines inmydataset in your default project —myproject.

SELECT*EXCEPT(is_typed)FROMmydataset.INFORMATION_SCHEMA.PARAMETERSWHEREtable_type='BASE TABLE';

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