The SHOW PROCEDURE STATUS statement displays information about stored procedures, including the database, name, type, creator, creation and modification dates, and character set.
Syntax
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]
Parameters
Parameter |
Note |
|---|---|
| LIKE 'pattern' | Optional. Specifies the pattern for the stored procedure name to match. You can use % and _ Wildcard. |
| WHERE expr | Optional. Use the WHERE clause to specify conditions for selecting rows. |
To use this statement, you must be the user defined by DEFINER and have the SHOW_ROUTINE privilege, global-level SELECT privilege, or the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine.
The SHOW PROCEDURE STATUS statement outputs the following information:
Db: The name of the database where the stored procedure is located.Name: The name of the stored procedure.Type: The type of the stored program. The value isPROCEDURE.Definer: The creator.Modified: The modification time.Created: The creation time.Security_type: The SQL security type.Comment: a comment.character_set_client: the value of thecharacter_set_clientsystem variable in the current session when the stored procedure is created.collation_connection: The value of thecollation_connectionsystem variable in the current session when the stored procedure was created.Database Collation: The collation of the database associated with the stored procedure.
Information about stored procedures can also be obtained from the INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES tables. For more information, see INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES.
Examples
View the status of all stored procedures.
obclient> SHOW PROCEDURE STATUS;
Query stored procedures that match a specific pattern:
obclient> SHOW PROCEDURE STATUS LIKE 'p%';
The query result is as follows:
+---------+--------------------+-----------+---------------------+----------------------------+----------------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+--------------------+-----------+---------------------+----------------------------+----------------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+
| test_db | proc | PROCEDURE | 'root'@'%' | 2025-12-23 11:01:31.863377 | 2025-12-23 11:01:31.863377 | DEFINER | NULL | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| test_db | proc_name | PROCEDURE | 'root'@'%' | 2025-12-23 16:06:27.599661 | 2025-12-23 16:06:27.599661 | DEFINER | NULL | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| test_db | process_order_data | PROCEDURE | 'admin'@'localhost' | 2025-12-22 16:50:27.144758 | 2025-12-22 16:50:27.144758 | INVOKER | Process order data with invoker security | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+---------+--------------------+-----------+---------------------+----------------------------+----------------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+