SHOW CREATE PROCEDURE
On this page
The SHOW CREATE PROCEDURE command shows the definition, procedure body, and other attributes for a stored procedure.
Refer to Permissions Matrix for the required permission.
Syntax
SHOW CREATE PROCEDURE [database_name.]procedure_name [WITH ID <variant ID>]
Remarks
-
If a procedure is overloaded,
SHOW CREATE PROCEDURErequires the procedure's variant ID which is available from SHOW FUNCTIONS.
Example - Show the Definition of a Stored Procedure
The following example shows the definition of a stored procedure named courses_:
SHOW CREATE PROCEDURE dbExample.courses_sp \G
Procedure: courses_sp
sql_mode: STRICT_ALL_TABLES,NO_AUTO_CREATE_USER
Create Procedure: CREATE OR REPLACE PROCEDURE `courses_sp`(course_code text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL, section_number int(11) NULL, number_students int(11) NULL) RETURNS void DEFINER = 's2user'@'%' AS
DECLARE
code TEXT = UCASE(course_code);
num_students INT = number_students + 1;
BEGIN
INSERT INTO courses VALUES (code, section_number, num_students);
END;
character_set_client: utf8mb4
collation_connection: utf8mb4_binExample - Show the Definition of an Overloaded Stored Procedure
The following shows the output of SHOW CREATE PROCEDURE for variant 0 of the output_ procedure from Overloaded Functions and Stored Procedures.
SHOW CREATE PROCEDURE output_type WITH ID 0;
*** 1. row ***
Procedure: output_type
sql_mode: STRICT_ALL_TABLES,NO_AUTO_CREATE_USER
Create Procedure: CREATE OR REPLACE PROCEDURE `output_type`(input int(11) NULL) RETURNS void DEFINER = 'root'@'%' AS
BEGIN
ECHO SELECT "4-byte Integer";
END;
character_set_client: utf8mb4
collation_connection: utf8mb4_binLast modified: