Skip to main content

SHOW PROCEDURE STATUS

The SHOW PROCEDURE STATUS statement displays information about stored procedures, including the database, name, type, creator, creation and modification dates, and character set.

The syntax of the SHOW PROCEDURE STATUS statement is as follows:

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

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

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

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 procedure.

  • 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 the character_set_client system variable in the current session when the stored procedure was created.

  • collation_connection: The value of the collation_connection system 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.

Here is an example of the SHOW PROCEDURE STATUS statement:

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