ROUTINE_COLUMNS

  • Article

Important

This feature is in Public Preview.

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

INFORMATION_SCHEMA.ROUTINE_COLUMNS lists the result columns of table valued functions within the catalog.

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

This relation is an extension to the SQL standard information schema.

Definition

The ROUTINE_COLUMNS relation contains the following columns:

Name Data type Nullable Description
SPECIFIC_CATALOG STRING No Catalog containing the routine.
SPECIFIC_SCHEMA STRING No Database (schema) containing the routine.
SPECIFIC_NAME STRING No Schema unique (specific) name of the routine.
ORDINAL_POSITION INTEGER No The position (1-based) of the column in the result column list.
COLUMN_NAME STRING Yes Name of the column, NULL if unnamed.
DATA_TYPE STRING No The parameter data type name.
FULL_DATA_TYPE STRING No The parameter data type definition, for example 'DECIMAL(10, 4)'.
CHARACTER_MAXIMUM_LENGTH INTEGER Yes Always NULL, reserved for future use.
CHARACTER_OCTET_LENGTH STRING Yes Always NULL, reserved for future use.
NUMERIC_PRECISION INTEGER 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 No For DECIMAL 10, for all other numeric types 2, NULL otherwise.
NUMERIC_SCALE INTEGER 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 For DATE 0, for TIMESTAMP, and INTERVAL … SECOND 3, any other INTERVAL 0, NULL otherwise.
INTERVAL_TYPE STRING Yes For INTERVAL the unit portion of the interval, e.g. 'YEAR TO MONTH', NULL otherwise.
INTERVAL_PRECISION INTERAL Yes Always NULL, reserved for future use.
MAXIMUM_CARDINALITY INTEGER Yes Always NULL, reserved for future use.
PARAMETER_DEFAULT STRING Yes Always NULL, reserved for future use.
COMMENT STRING Yes An optional comment describing the result column.

Constraints

The following constraints apply to the ROUTINE_COLUMNS relation:

Class Name Column List Description
Primary key RTN_COLS_PK SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION Uniquely identifies the result column.
Foreign key RTN_COLS_ROUTINES_FK SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME References ROUTINES

Examples

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