PARAMETERS

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above check marked yes Unity Catalog only

Important

This feature is in Public Preview.

INFORMATION_SCHEMA.PARAMETERS lists the routine parameters within the catalog.

The rows returned are limited to the routines the user is privileged to interact with.

Definition

The PARAMETERS relation contains the following columns:

Name Data type Nullable Standard Description
SPECIFIC_CATALOG STRING No Yes Catalog containing the routine.
SPECIFIC_SCHEMA STRING No Yes Database (schema) containing the routine.
SPECIFIC_NAME STRING No Yes Schema unique (specific) name of the routine.
ORDINAL_POSITION INTEGER No Yes The position (1-based) of the parameter in the routine parameter list.
PARAMETER_MODE STRING No Yes Always 'IN'. Reserved for future use.
IS_RESULT STRING No Yes Always 'NO'. Reserved for future use.
AS_LOCATOR STRING No Yes Always 'NO'. Reserved for future use.
PARAMETER_NAME STRING Yes Yes Name of the parameters, NULL if unnamed.
DATA_TYPE STRING No Yes The parameter data type name.
FULL_DATA_TYPE STRING No No The parameter data type definition, for example 'DECIMAL(10, 4)'.
CHARACTER_MAXIMUM_LENGTH INTEGER Yes Yes Always NULL, reserved for future use.
CHARACTER_OCTET_LENGTH STRING Yes Yes Always NULL, reserved for future use.
NUMERIC_PRECISION INTEGER Yes Yes For base-2 integral numeric types, FLOAT, and DOUBLE, the number of supported bits. For DECIMAL the number of digits, NULL otherwise.
NUMERIC_PRECISION_RADIX INTEGER Yes Yes For DECIMAL 10, for all other numeric types 2, NULL otherwise.
NUMERIC_SCALE INTEGER Yes Yes For integral numeric types 0, for DECIMAL the number of digits to the right of the decimal point, NULL otherwise.
DATETIME_PRECISION INTEGER Yes Yes For DATE 0, for TIMESTAMP, and INTERVAL … SECOND 3, any other INTERVAL 0, NULL otherwise.
INTERVAL_TYPE STRING Yes Yes For INTERVAL the unit portion of the interval, e.g. 'YEAR TO MONTH', NULL otherwise.
INTERVAL_PRECISION INTERAL Yes Yes Always NULL, reserved for future use.
MAXIMUM_CARDINALITY INTEGER Yes Yes Always NULL, reserved for future use.
PARAMETER_DEFAULT STRING Yes Yes Always NULL, reserved for future use.
COMMENT STRING Yes No An optional comment describing the parameter.

Constraints

The following constraints apply to the PARAMETERS relation:

Class Name Column List Description
Primary key PARAMETERS_PK SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION Uniquely identifies the parameter.
Foreign key PARAMETERS_ROUTINES_FK SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME References ROUTINES_PK

Examples

> SELECT parameter_name, data_type
    FROM information_schema.parameters
    WHERE specific_schema = 'default'
      AND specific_name = 'foo'
    ORDER BY ordinal_position;