The INFORMATION_SCHEMA ROUTINES dictionary view provides information about stored routines (stored procedures and stored functions), but does not include built-in (local) functions or loadable functions. The INFORMATION_SCHEMA ROUTINES view contains the following columns.
Field |
Description |
|---|---|
| SPECIFIC_NAME | The name of the stored routine. |
| ROUTINE_CATALOG | The name of the directory to which the stored routine belongs. This value is always def. This field is currently not used. |
| ROUTINE_SCHEMA | The name of the schema (database) to which the stored routine belongs. |
| ROUTINE_NAME | The name of the stored routine, which corresponds to the SPECIFIC_NAME The same. |
| ROUTINE_TYPE | Type of the stored routine. Stored procedure is PROCEDURE, the stored function is FUNCTION. |
| DATA_TYPE | If the routine is a stored function, the return value is a data type; if the routine is a stored procedure, the return value is empty. DATA_TYPE The value is only the type name and contains no other information. DTD_IDENTIFIER The value contains the type name and other information, such as precision or length. This field is currently not used. |
| CHARACTER_MAXIMUM_LENGTH | The maximum length of a string return value of a stored function, in characters. For a stored procedure, this value is NULL. This field is currently unused. |
| CHARACTER_OCTET_LENGTH | The maximum length of a string return value of a stored function, in bytes. If the routine is a stored procedure, this value is NULL. This field is currently unused. |
| NUMERIC_PRECISION | The numeric range of the stored function's return value. For a stored procedure, this value is NULL. This field is currently unused. |
| NUMERIC_SCALE | The numerical precision of the stored function's numeric return value. If the routine is a stored procedure, this value is NULL. This field is currently unused. |
| DATETIME_PRECISION | The second-precision fractional part of the time returned by a stored function. For a stored procedure, this value is NULL. This field is currently not used. |
| CHARACTER_SET_NAME | The character set name of the string return value of the stored function. If the routine is a stored procedure, this value is NULL. This field is currently not used. |
| COLLATION_NAME | The name of the collation for sorting string return values of stored functions. For routines that are stored procedures, this value is NULL. This field is currently not used. |
| DTD_IDENTIFIER | If the routine is a stored function, the return value is a data type. If the routine is a stored procedure, this value is empty. DATA_TYPE The value is only the type name and does not contain other information. DTD_IDENTIFIER The value 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 statement executed by the routine. |
| EXTERNAL_NAME | This value is always NULL. |
| EXTERNAL_LANGUAGE | The language of the stored routine. This field is currently unused. |
| PARAMETER_STYLE | This value is always SQL. |
| IS_DETERMINISTIC | Valid values: YES or NO, depending on whether the storage routine uses DETERMINISTIC The definition of the feature. This field is not currently used. |
| SQL_DATA_ACCESS | 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 | Routine's SQL SECURITY Feature. This value is DEFINER or INVOKER. This field is not currently used. |
| CREATED | Date and time when the routine was created. This column is of the TIMESTAMP. |
| LAST_ALTERED | The date and time when the routine was last modified. If the procedure has not been modified since its creation, this value is the same as the creation date and time. CREATED The values are the same. |
| SQL_MODE | Create or change the SQL mode that is valid during routine execution and execute the routine in that mode. |
| ROUTINE_COMMENT | The comment text of the routine, if any. Otherwise, this value is empty. |
| DEFINER | In DEFINER The account specified in the clause (usually the user who created the routine), in the format of 'user_name'@'host_name'. |
| CHARACTER_SET_CLIENT | System variables when creating or modifying a storage routine CHARACTER_SET_CLIENT The value of the current session. |
| COLLATION_CONNECTION | System variables when creating or modifying a stored routine COLLATION_CONNECTION The value of the current session. |
| DATABASE_COLLATION | System variables when creating or changing a storage routine DATABASE_COLLATION The value of the current session. |
An example is as follows:
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