The SHOW CREATE PROCEDURE statement is used to display information about created stored programs.
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 DEFINER and have the SHOW_ROUTINE privilege, global-level SELECT privilege, or the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine. If you only have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege, the value in the Create Procedure or Create Function field is displayed as NULL.
The SHOW CREATE PROCEDURE statement outputs the following information:
Procedure: the name of the stored procedure.sql_mode: The SQL mode that takes effect when executing the stored procedure.Create Procedure: TheCREATE PROCEDUREstatement that defines the stored procedure.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 is 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 ROUTINES table. For more information, see INFORMATION_SCHEMA PARAMETERS.
The following example shows the SHOW CREATE PROCEDURE statement:
obclient> SHOW CREATE PROCEDURE proc_name;
The return result 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