Note
This view is available starting with V2.2.77.
Purpose
This view displays information about stored procedures in the current tenant.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| SPECIFIC_NAME | varchar(64) | NO | The name of the stored procedure. |
| ROUTINE_CATALOG | varchar(512) | NO | The name of the catalog to which the stored procedure belongs. This column is not currently used, and its value is 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 | The type of the routine.
|
| DATA_TYPE | varchar(64) | NO | If the routine is a stored function, this column returns the data type. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| CHARACTER_MAXIMUM_LENGTH | bigint(20) | NO | The maximum length of the string return value of the stored function, in characters. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| CHARACTER_OCTET_LENGTH | bigint(20) | NO | The maximum length of the string return value of the stored function, in bytes. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| NUMERIC_PRECISION | bigint(20) unsigned | NO | The numeric precision of the numeric return value of the stored function. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| NUMERIC_SCALE | bigint(20) | NO | The numeric scale of the numeric return value of the stored function. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| DATETIME_PRECISION | bigint(20) unsigned | NO | The second precision of the time return value of the stored function. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| CHARACTER_SET_NAME | varchar(64) | NO | The name of the character set of the string return value of the stored function. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| COLLATION_NAME | varchar(64) | NO | The collation of the string return value of the stored function. If the routine is a stored procedure, this column is NULL. This column is not currently used. |
| DTD_IDENTIFIER | longtext | NO | This column is not currently used. If the routine is a stored function, this column returns the data type. If the routine is a stored procedure, this column is NULL. The value of DATA_TYPE is only the type name, without additional information. The value of DTD_IDENTIFIER includes the type name and other information, such as precision or length. |
| ROUTINE_BODY | varchar(8) | NO | The language used to define the routine. This value is always SQL. |
| ROUTINE_DEFINITION | longtext | NO | The text information of the stored procedure definition. |
| EXTERNAL_NAME | varchar(64) | NO | This value is always NULL. |
| EXTERNAL_LANGUAGE | varchar(64) | NO | The language of the stored procedure. This column is not currently 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 is defined with the DETERMINISTIC characteristic. This column is not currently used. |
| SQL_DATA_ACCESS | varchar(64) | NO | The data access characteristic of the stored procedure. This column is not currently used. |
| SQL_PATH | varchar(64) | NO | This value is always NULL. |
| SECURITY_TYPE | varchar(7) | NO | The SQL security type:
|
| CREATED | datetime | YES | The date and time when the stored procedure was created. |
| LAST_ALTERED | datetime | YES | The date and time when the stored procedure was last modified. If the stored procedure has not been modified since it was created, this value is the same as the CREATED value. |
| SQL_MODE | text | NO | The SQL mode in effect when the procedure was created or modified. This mode is used to execute the procedure. |
| ROUTINE_COMMENT | longtext | NO | The comment text. |
| DEFINER | varchar(93) | NO | The account specified in the DEFINER clause (usually the user who created the procedure). |
| CHARACTER_SET_CLIENT | varchar(32) | NO | The session value of the system variable CHARACTER_SET_CLIENT at the time the stored procedure was created or modified. |
| COLLATION_CONNECTION | varchar(32) | NO | The session value of the system variable COLLATION_CONNECTION at the time the stored procedure was created or modified. |
| DATABASE_COLLATION | varchar(32) | NO | The session value of the system variable DATABASE_COLLATION at the time the stored procedure was created or modified. |
Sample query
Query the 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