INFORMATION_SCHEMA ROUTINES
The INFORMATION_SCHEMA ROUTINES dictionary view provides information about stored routines (stored procedures and stored functions) but does not include information about built-in (native) 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. This value is always def. This field is not currently used. |
| ROUTINE_SCHEMA | The name of the schema (database) to which the stored routine belongs. |
| ROUTINE_NAME | The name of the stored routine, which is the same as SPECIFIC_NAME. |
| ROUTINE_TYPE | The type of the stored routine. PROCEDURE indicates a stored procedure, and FUNCTION indicates a stored function. |
| DATA_TYPE | If the routine is a stored function, this column returns the data type of the return value. If the routine is a stored procedure, this column returns NULL. DATA_TYPE contains only the type name, not other information. DTD_IDENTIFIER contains the type name and other information, such as precision or length. This field is not currently used. |
| CHARACTER_MAXIMUM_LENGTH | The maximum length of the return value of the stored function, in characters. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| CHARACTER_OCTET_LENGTH | The maximum length of the return value of the stored function, in bytes. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| NUMERIC_PRECISION | The numeric range of the return value of the stored function. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| NUMERIC_SCALE | The numeric precision of the return value of the stored function. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| DATETIME_PRECISION | The precision of the fractional part of the return value of the stored function in seconds. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| CHARACTER_SET_NAME | The character set of the return value of the stored function. If the routine is a stored procedure, this column returns NULL. This field is not currently used |
| COLLATION_NAME | The collation of the return value of the stored function. If the routine is a stored procedure, this column returns NULL. This field is not currently used. |
| DTD_IDENTIFIER | If the routine is a stored function, this column returns the data type of the return value. If the routine is a stored procedure, this column returns NULL. DATA_TYPE contains only the type name, not other information. DTD_IDENTIFIER contains the type name and other information, such as precision or length. |
| ROUTINE_BODY | The language used to define the routine. This value is always SQL. |
| ROUTINE_DEFINITION | The text of the SQL statements executed by the routine. |
| EXTERNAL_NAME | This value is always NULL. |
| EXTERNAL_LANGUAGE | The language of the stored routine. This field is not currently used. |
| PARAMETER_STYLE | This value is always SQL. |
| IS_DETERMINISTIC | YES or NO, depending on whether the stored routine uses the DETERMINISTIC characteristic. This field is not currently used. |
| SQL_DATA_ACCESS | The data access characteristics of the routine. This value can be CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA. |
| SQL_PATH | This value is always NULL. |
| SECURITY_TYPE | The SQL SECURITY characteristic of the routine. This value is DEFINER or INVOKER. This field is not currently used. |
| CREATED | The date and time when the routine was created. This value is of the TIMESTAMP type. |
| LAST_ALTERED | The date and time when the routine was last modified. If the routine has not been modified since it was created, this value is the same as the CREATED value. |
| SQL_MODE | The SQL mode in effect when the routine was created or modified, and in which the routine is executed. |
| ROUTINE_COMMENT | The comment text of the routine, if any. Otherwise, this column returns NULL. |
| DEFINER | The account specified in the DEFINER clause, which is typically the user who created the routine, in the format 'user_name'@'host_name'. |
| CHARACTER_SET_CLIENT | The value of the system variable CHARACTER_SET_CLIENT in the current session when the stored routine was created or modified. |
| COLLATION_CONNECTION | The value of the system variable COLLATION_CONNECTION in the current session when the stored routine was created or modified. |
| DATABASE_COLLATION | The value of the system variable DATABASE_COLLATION in the current session when the stored routine was created or modified. |
Here is an example:
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