SHOW PROCEDURE STATUS

You can use the SHOW PROCEDURE STATUS statement to view the information about a stored procedure, such as the name and the collation of the database, and the name, type, creator, and time of creation and modification of the stored procedure.

The syntax is as follows:

SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]

To use this statement, you must be the user defined by DEFINER and have the SHOW_ROUTINE privilege and the global SELECT privilege, or the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on this routine.

The LIKE clause (if any) specifies the name of the procedure to match. You can also use the WHERE clause to select rows.

The output of the SHOW PROCEDURE STATUS statement contains the following fields:

• Db: the name of the database associated with the stored program.

• Name: the name of the stored program.

• Type: the type of the stored program.

• Definer: the creator of the stored program.

• Modified: the time when the stored program was modified.

• Created: the time when the stored program was created.

• Security_type: the SQL mode.

• Comment: the comment.

• character_set_client: the value of the character_set_client system variable in the current session when the stored program was created.

• collation_connection: the value of the collation_connection system variable in the current session when the stored program was created.

• Database Collation: the collation of the database to which the stored program is associated.

You can also obtain the information about a stored program from the INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES tables. For more information, see INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES.

Here is an example:

obclient> SHOW PROCEDURE STATUS LIKE 'p1'\G
*************************** 1. row ***************************
                  Db: test
                Name: p1
                Type: PROCEDURE
             Definer: 'root'@'%'
            Modified: 2022-05-26 18:07:51.994639
             Created: 2022-05-26 18:07:51.994639
       Security_type: DEFINER
             Comment: NULL
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci