Skip to main content

SHOW CREATE PROCEDURE

The SHOW CREATE PROCEDURE statement displays information about a stored procedure.

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

SHOW CREATE PROCEDURE proc_name

To use this statement, you must be the user defined by the DEFINER clause, have the SHOW_ROUTINE privilege, have the global SELECT privilege, or have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine. If you have only the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege, the value of the Create Procedure or Create Function field is NULL.

The SHOW CREATE PROCEDURE statement returns the following information:

  • Procedure: the name of the stored procedure.

  • sql_mode: the SQL mode in effect when the stored procedure is executed.

  • Create Procedure: the CREATE PROCEDURE statement used to define the stored procedure.

  • 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 ROUTINES table. For more information, see INFORMATION_SCHEMA PARAMETERS.

Here is an example of the SHOW CREATE PROCEDURE statement:

SHOW CREATE PROCEDURE proc_name;

The output is as follows:

+-------------+----------+-----------------+-----------------------+----------------------+--------------------+
| Procedure | sql_mode  |  Create Procedure | character_set_client  | collation_connection | Database Collation |
+----------+----------+-----------------+-----------------------+----------------------+-----------------------+
| proc_name | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE  |CREATE PROCEDURE `test`.`proc_name`() BEGIN     DECLARE var_name VARCHAR(20) DEFAULT 'ZhangSan';       SET var_name = 'LiSi';      SELECT var_name;   END  |       utf8mb4         | utf8mb4_general_ci   | utf8mb4_general_ci |
+----------+----------+-----------------+-----------------------+----------------------+-----------------------+
1 row in set