SHOW CREATE PROCEDURE proc_name
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, SHOW CREATE FUNCTION
, displays information about stored functions.
Both statements require that you are the owner of the routine or have the SELECT
privilege on the mysql.proc
table. When neither is true, the statements display NULL
for the Create Procedure
or Create Function
field.
Warning Users with SELECT
privileges on mysql.proc
or USAGE
privileges on *.*
can view the text of routines, even when they do not have privileges for the function or procedure itself.
The output of these statements is unreliably affected by the sql_quote_show_create
server system variable - see http://bugs.mysql.com/bug.php?id=12719
Here's a comparison of the SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
statements.
SHOW 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_ci
When the user issuing the statement does not have privileges on the routine, attempting to CALL
the procedure raises Error 1370.
CALL test.prc1(); Error 1370 (42000): execute command denieed to user 'test_user'@'localhost' for routine 'test'.'prc1'
If the user neither has privilege to the routine nor the SELECT
privilege on mysql.proc
table, it raises Error 1305, informing them that the procedure does not exist.
SHOW CREATE TABLES test.prc1\G Error 1305 (42000): PROCEDURE prc1 does not exist
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/show-create-procedure/