ROUTINES

  • Article

Important

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.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;