ROUTINE_COLUMNS

Preview

This feature is in Public Preview.

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS 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;