ROUTINES
Preview
This feature is in Public Preview.
Applies to: 
Databricks SQL Databricks Runtime 11.3 LTS and above 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
SQL
> SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE specific_schema = 'default'
ORDER BY routine_name;