SHOW CREATE PROCEDURE

You can use the SHOW CREATE PROCEDURE statement to view the information about a stored procedure.

The syntax 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 and the global SELECT privilege, or the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on this 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 output of the SHOW CREATE PROCEDURE statement contains the following fields:

• Procedure: the name of the stored procedure.

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

• Create Procedure: the CREATE PROCEDURE statement that defines 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 to which the stored procedure is associated.

You can also obtain the information about a stored procedure from the INFORMATION_SCHEMA ROUTINES table. For more information, see INFORMATION_SCHEMA PARAMETERS.

Here is an example:

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