Syntax
------ 
SHOW CREATE PROCEDURE proc_name
 
Description
----------- 
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
 
Examples
-------- 
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
 


URL: https://mariadb.com/kb/en/show-create-procedure/