Overview
Displays information related to stored procedures in the current tenant.
Columns
Field Name |
Type |
Whether can be NULL |
Description |
|---|---|---|---|
| SPECIFIC_NAME | varchar(64) | NO | The name of the stored procedure. |
| ROUTINE_CATALOG | varchar(512) | NO | The name of the directory to which the stored procedure belongs. This field is currently unused and has a default value of 'def'. |
| ROUTINE_SCHEMA | varchar(64) | NO | The name of the schema to which the stored procedure belongs. |
| ROUTINE_NAME | varchar(64) | NO | The name of the stored procedure, which is the same as SPECIFIC_NAME. |
| ROUTINE_TYPE | varchar(9) | NO | Procedure type:
|
| DATA_TYPE | varchar(64) | NO | If the procedure is a stored function, the return value is of a data type; if the procedure is a stored procedure, this value is empty. This field is currently not used. |
| CHARACTER_MAXIMUM_LENGTH | bigint(20) | NO | Maximum length of the string return value of a stored function, in characters. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| CHARACTER_OCTET_LENGTH | bigint(20) | NO | Maximum length of the string return value of a stored function, in bytes. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| NUMERIC_PRECISION | bigint(20) unsigned | NO | The numeric precision of the return value of the stored function. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| NUMERIC_SCALE | bigint(20) | NO | The numeric scale of the return value of the stored function. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| DATETIME_PRECISION | bigint(20) unsigned | NO | The number of seconds in the time returned by the stored function. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| CHARACTER_SET_NAME | varchar(64) | NO | The character set name of the string return value of the stored function. If the procedure is a stored procedure, this value is NULL. This field is currently not used. |
| COLLATION_NAME | varchar(64) | NO | The category 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. |
| DTD_IDENTIFIER | longtext | NO | Not used. If the procedure is a stored function, the return value is of a data type. If the procedure is a stored procedure, this value is empty. DATA_TYPE The value of the IDT_TYPE field is only the type name without any other information, while the value of the DTD_IDENTIFIER field contains the type name and possibly other information, such as precision or length. |
| ROUTINE_BODY | varchar(8) | NO | The language used to define the procedure. This value is always SQL. |
| ROUTINE_DEFINITION | longtext | NO | The text information that defines the stored procedure. |
| EXTERNAL_NAME | varchar(64) | NO | This value is always NULL. |
| EXTERNAL_LANGUAGE | varchar(64) | NO | Language of the stored procedure. This field is currently not used. |
| PARAMETER_STYLE | varchar(8) | NO | This value is always SQL. |
| IS_DETERMINISTIC | varchar(3) | NO | YES or NO, depending on whether the stored procedure uses DETERMINISTIC Feature definition. This field is not currently used. |
| SQL_DATA_ACCESS | varchar(64) | NO | Not used. Data access characteristics of a stored procedure. |
| SQL_PATH | varchar(64) | NO | This value is always NULL. |
| SECURITY_TYPE | varchar(7) | NO | SQL Security Type:
|
| CREATED | datetime | YES | The date and time when the stored procedure was created. |
| LAST_ALTERED | datetime | YES | The last modification date and time of the stored procedure. If the procedure has not been modified since its creation, this value is the same as the CREATED value. |
| SQL_MODE | text | NO | The SQL mode that is valid when creating or altering the procedure, and in which the procedure is executed. |
| ROUTINE_COMMENT | longtext | NO | Comment text |
| DEFINER | varchar(93) | NO | The account named in the DEFINER clause, which is typically the user who creates the procedure. |
| CHARACTER_SET_CLIENT | varchar(32) | NO | System variables when creating or altering a stored procedure CHARACTER_SET_CLIENT The session value of the specified key. |
| COLLATION_CONNECTION | varchar(32) | NO | System variables when creating or altering a stored procedure COLLATION_CONNECTION The session value of the specified key. |
| DATABASE_COLLATION | varchar(32) | NO | System variables when creating or altering a stored procedure DATABASE_COLLATION The session value of the specified parameter. |
Sample query
View information about stored procedures in the current tenant.
obclient[test]> SELECT * FROM information_schema.ROUTINES\G
The query result is as follows:
*************************** 1. row ***************************
SPECIFIC_NAME: citycount
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: citycount
ROUTINE_TYPE: PROCEDURE
DATA_TYPE: NULL
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
SELECT COUNT(*) INTO cities FROM city
WHERE CountryCode = country;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: READS_SQL_DATA
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2025-05-07 14:58:18
LAST_ALTERED: 2025-05-07 14:58:18
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER
ROUTINE_COMMENT:
DEFINER: 'root'@'%'
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8mb4_general_ci
1 row in set