The INFORMATION_SCHEMA ROUTINES dictionary view provides information about stored routines such as stored procedures and storage functions, which do not include built-in (local) functions or loadable functions. The following table describes the fields in the INFORMATION_SCHEMA ROUTINES dictionary view.
| Field | Description |
|---|---|
| SPECIFIC_NAME | The name of the stored routine. |
| ROUTINE_CATALOG | The name of the catalog to which the stored routine belongs. The value of this field is always def. At present, this field is not used. |
| ROUTINE_SCHEMA | The name of the schema (or database) to which the stored routine belongs. |
| ROUTINE_NAME | The name of the stored routine, which is the same as the SPECIFIC_NAME. |
| ROUTINE_TYPE | The type of the stored routine. For a stored procedure, the value is PROCEDURE. For a storage function, the value is FUNCTION. |
| DATA_TYPE | The data type of the return value of the routine if the routine is a stored function. If the routine is a stored procedure, this field is empty. The value of DATA_TYPE contains only the type name, without other information. The value of DTD_IDENTIFIER contains the data type name and other information, such as the precision or length. At present, this field is not used. |
| CHARACTER_MAXIMUM_LENGTH | The maximum character length for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| CHARACTER_OCTET_LENGTH | The maximum length for string return values of the routine if the routine is a stored function, in bytes. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| NUMERIC_PRECISION | The numeric precision of the return value of the stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| NUMERIC_SCALE | The numeric scale of the return value of the stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| DATETIME_PRECISION | The fractional seconds precision of the temporal return value of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| CHARACTER_SET_NAME | The character set name for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| COLLATION_NAME | The collation name for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this field is NULL. At present, this field is not used. |
| DTD_IDENTIFIER | The data type of the return value of the routine if the routine is a stored function. If the routine is a stored procedure, this field is empty. The value of DATA_TYPE contains only the type name, without other information. The value of DTD_IDENTIFIER contains the data type name and other information, such as the precision or length. |
| ROUTINE_BODY | The language used for the routine definition. The value of this field is always SQL. |
| ROUTINE_DEFINITION | The text of the SQL statement executed by the routine. |
| EXTERNAL_NAME | The value of this field is always NULL. |
| EXTERNAL_LANGUAGE | The language of the stored routine. At present, this field is not used. |
| PARAMETER_STYLE | The value of this field is always SQL. |
| IS_DETERMINISTIC | Indicates whether the stored routine is defined with the DETERMINISTIC characteristic. Valid value: YES or NO. At present, this field is not used. |
| SQL_DATA_ACCESS | The data access characteristic for the stored routine. The value can be CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA. |
| SQL_PATH | The value of this field is always NULL. |
| SECURITY_TYPE | The SQL SECURITY characteristic of the routine. The value can be DEFINER or INVOKER. At present, this field is not used. |
| CREATED | The date and time when the routine was created. The value type is TIMESTAMP. |
| LAST_ALTERED | The date and time when the routine was last modified. If the routine has not been modified since its creation, the value of this field is the same as the value of CREATED. |
| SQL_MODE | The SQL mode in effect when the routine was created or modified and under which the routine executes. |
| ROUTINE_COMMENT | The comment text of the routine, if any. The value can be empty. |
| DEFINER | The account named in the DEFINER clause, in the format of 'user_name'@'host_name'. It is often the user who created the routine. |
| CHARACTER_SET_CLIENT | The value of the CHARACTER_SET_CLIENT system variable in the current session when the stored routine was created or modified. |
| COLLATION_CONNECTION | The value of the COLLATION_CONNECTION system variable in the current session when the stored routine was created or modified. |
| DATABASE_COLLATION | The value of the DATABASE_COLLATION system variable in the current session when the stored routine was created or modified. |
Here is an example:
obclient> SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='p1'\G
*************************** 1. row ***************************
SPECIFIC_NAME: p1
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: p1
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2022-05-18 18:07:51.994639
LAST_ALTERED: 2022-05-26 18:07:51.994639
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE
ROUTINE_COMMENT:
DEFINER: 'root'@'%'
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8mb4_general_ci