ROUTINES

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.ROUTINES lists the routines (functions) within the catalog.

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

Definition

The ROUTINES 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.

ROUTINE_CATALOG

STRING

No

Yes

Matches SPECIFIC_CATALOG.

ROUTINE_SCHEMA

STRING

No

Yes

Matches SPECIFIC_SCHEMA.

ROUTINE_NAME

STRING

No

Yes

Name of the routine.

ROUTINE_TYPE

STRING

No

Yes

Always 'FUNCTION'. Reserved for future use.

DATA_TYPE

STRING

No

Yes

The result data type name, or 'TABLE' for a table valued function.

FULL_DATA_TYPE

STRING

No

No

The result 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.

ROUTINE_BODY

STRING

No

Yes

'SQL' or 'PYTHON'.

ROUTINE_DEFINITION

STRING

Yes

Yes

The full definition of the routine. NULL if the user is not the owner.

EXTERNAL_NAME

STRING

Yes

Yes

Always NULL, reserved for future use.

EXTERNAL_LANGUAGE

STRING

Yes

Yes

Always NULL, reserved for future use.

PARAMETER_STYLE

STRING

Yes

Yes

'SQL', reserved for future use.

IS_DETERMINISTIC

STRING

No

Yes

'YES' if routine defined as deterministic, 'NO' otherwise.

SQL_DATA_ACCESS

STRING

No

Yes

'READS SQL DATA' if routine reads from a relation, 'CONSTAINS SQL' otherwise.

IS_NULL_CALL

STRING

Yes

Yes

Always 'YES', reserved for future use.

SQL_PATH

STRING

Yes

Yes

Always NULL, reserved for future use.

SECURITY_TYPE

STRING

No

Yes

Always 'DEFINER', reserved for future use.

AS_LOCATOR

STRING

No

Yes

Always 'NO', reserved for future use.

COMMENT

STRING

Yes

No

An optional comment describing the routine.

CREATED

TIMESTAMP

No

No

Timestamp when the routine was created.

CREATED_BY

STRING

No

No

Principal which created the routine.

LAST_ALTERED

TIMESTAMP

No

No

Timestamp when the routine definition was last altered in any way.

LAST_ALTERED_BY

STRING

No

No

Principal which last altered the routine.

Constraints

The following constraints apply to the ROUTINES relation:

Class

Name

Column List

Description

Primary key

ROUTINES_PK

SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME

Uniquely identifies the routine.

Foreign key

ROUTINES_SCHEMATA_FK

SPECIFIC_CATALOG, SPECIFIC_SCHEMA

References SCHEMATA

Examples 

> SELECT routine_name, routine_definition
    FROM information_schema.routines
    WHERE specific_schema = 'default'
    ORDER BY routine_name;