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 | Description |
|---|---|
| LIKE 'pattern' | Optional. Specifies the pattern to match the stored procedure name. You can use the % and _ wildcards. |
| WHERE expr | Optional. Specifies the condition to select rows using the WHERE clause. |
To use this statement, you must be the user defined by the DEFINER and have the SHOW_ROUTINE privilege, have the global SELECT privilege, or have 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, which isPROCEDURE.Definer: The creator.Modified: The modification time.Created: The creation time.Security_type: The SQL security type.Comment: The comment.character_set_client: The value of thecharacter_set_clientsystem variable in the current session when the stored procedure was 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.
You can also obtain information about stored procedures 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;
View 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 |
+---------+--------------------+-----------+---------------------+----------------------------+----------------------------+---------------+------------------------------------------+----------------------+----------------------+--------------------+