Get the CREATE PROCEDURE statement. This statement returns the SQL syntax used to define a specific stored procedure.
This statement is a MariaDB extension. It returns the exact string that can be used to re-create the named stored procedure, as well as the SQL_MODE that was used when the trigger has been created and the character set used by the connection.. A similar statement, , displays information about .
Both statements require that:
you are the owner of the routine;
you have the privilege (from ); or
you have the privilege on the table.
When none of the above statements are true, the statements display NULL for the Create Procedure or Create Function field.
Users with SELECT privileges on or USAGE privileges on *.* can view the text of routines, even when they do not have privileges for the function or procedure itself.
SHOW CREATE PROCEDURE quotes identifiers, according to the value of the system variable.
SHOW CREATE PROCEDURE quotes identifiers, according to the value of the system variable. The output of this statement is unreliably affected by the system variable.
Here's a comparison of the SHOW CREATE PROCEDURE and statements.
When the user issuing the statement does not have privileges on the routine, attempting to the procedure raises Error 1370.
If the user neither has privilege to the routine nor the privilege on table, it raises Error 1305, informing them that the procedure does not exist.
This page is licensed: GPLv2, originally from
SHOW CREATE PROCEDURE proc_nameSHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
Procedure: simpleproc
sql_mode:
Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
Function: hello
sql_mode:
Create Function: CREATE FUNCTION `hello`(s CHAR(20))
RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ciCALL test.prc1();
Error 1370 (42000): execute command denied to
user 'test_user'@'localhost' for routine 'test'.'prc1'SHOW CREATE TABLES test.prc1\G
Error 1305 (42000): PROCEDURE prc1 does not exist