PARAMETERS

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above 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	'IN', 'INOUT', or 'OUT'.
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.

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	'IN', 'INOUT', or 'OUT'.
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

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

SQL

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

Related

• Information schema
• INFORMATION_SCHEMA.ROUTINE_COLUMNS
• INFORMATION_SCHEMA.ROUTINES