PARAMETERS

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

Preview

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;