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 currently not 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 procedure:
|
| DATA_TYPE | varchar(64) | NO | If the procedure is a stored function, the value is the data type. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| CHARACTER_MAXIMUM_LENGTH | bigint(20) | NO | The 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 column is currently not used. |
| CHARACTER_OCTET_LENGTH | bigint(20) | NO | The 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 column is currently not used. |
| NUMERIC_PRECISION | bigint(20) unsigned | NO | The numeric precision of the numeric return value of a stored function. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| NUMERIC_SCALE | bigint(20) | NO | The numeric scale of the numeric return value of a stored function. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| DATETIME_PRECISION | bigint(20) unsigned | NO | The second precision of the time return value of a stored function. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| CHARACTER_SET_NAME | varchar(64) | NO | The character set name of the string return value of a stored function. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| COLLATION_NAME | varchar(64) | NO | The collation name of the string return value of a stored function. If the procedure is a stored procedure, this value is NULL. This column is currently not used. |
| DTD_IDENTIFIER | longtext | NO | Not used. If the procedure is a stored function, the value is the data type. If the procedure is a stored procedure, this value is NULL. The DATA_TYPE value is only the type name, without additional information; while the DTD_IDENTIFIER value contains the type name along with possible additional 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 of the stored procedure definition. |
| EXTERNAL_NAME | longtext | NO | The location and entry point of the external stored procedure. |
| EXTERNAL_LANGUAGE | varchar(64) | NO | The language and type of the external stored procedure. |
| 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 currently not used. |
| SQL_DATA_ACCESS | varchar(64) | NO | Not used. The data access characteristics of the stored procedure. |
| 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 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 was effective when the procedure was created or modified, in which the procedure is executed. |
| 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