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 stored procedure:
|
| DATA_TYPE | varchar(64) | NO | If the procedure is a stored function, this column returns the data type of the function's return value. If the procedure is a stored procedure, this column is empty. This column is not currently used. |
| CHARACTER_MAXIMUM_LENGTH | bigint(20) | NO | The maximum length of the return value of the stored function, in characters. If the procedure 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 return value of the stored function, in bytes. If the procedure 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 return value of the stored function. If the procedure is a stored procedure, this column is NULL. This column is not currently 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 column is NULL. This column is not currently used. |
| DATETIME_PRECISION | bigint(20) unsigned | NO | The second precision of the return value of the stored function. If the procedure is a stored procedure, this column is NULL. This column is not currently used. |
| CHARACTER_SET_NAME | varchar(64) | NO | The character set of the return value of the stored function. If the procedure is a stored procedure, this column is NULL. This column is not currently used. |
| COLLATION_NAME | varchar(64) | NO | The collation of the return value of the stored function. If the procedure 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 procedure is a stored function, this column returns the data type of the function's return value. If the procedure is a stored procedure, this column is empty. The value of DATA_TYPE is only the type name, without additional information. The value of DTD_IDENTIFIER includes 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 definition of the stored procedure. |
| 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 characteristics 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 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 when 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 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