View stored procedure/function definition in MySQL

Question

What is the MySQL command to view the definition of a stored procedure or function, similar to sp_helptext in Microsoft SQL Server?

I know that SHOW PROCEDURE STATUS will display the list of the procedures available. I need to see a single procedure's definition.

3

SELECT * FOM mysql.proc\G; – zloctb Mar 27 '16 at 15:24

score 148 · Accepted Answer · edited Jul 06 '14 at 23:29

148

SHOW CREATE PROCEDURE <name>

Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. Swap PROCEDURE for FUNCTION for a stored function.

edited Jul 06 '14 at 23:29

David Harkness

35,992
10
112
134

answered Dec 28 '09 at 10:13

afftee

2,221
1
16
14

i am getting `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1` – Govind Singh Apr 30 '14 at 04:43
+1 This has helped me three times now. One of these days I will remember the command by heart. – Registered User Jul 02 '14 at 20:34
4

This will not work unless Mysql was compiled --with-debug. So it does not work on AWS RDS instances. Try show create procedure 'xxxx'; as @valli below – ChrisR Feb 13 '18 at 09:42

score 44 · Answer 2 · edited Sep 12 '11 at 16:20

44

You can use this:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

edited Sep 12 '11 at 16:20

Frank V

25,141
34
106
144

answered Dec 28 '09 at 10:53

Haim Evgi

123,187
45
217
223

10

Unfortunately the ROUTINE_DEFINITION does not include the IN/OUT parameters of the stored procedure nor the return values (while SHOW CREATE PROCEDURE does). If you want to get those, you can query straight against mysql.proc, e.g. SELECT param_list,returns,body FROM mysql.proc WHERE db='yourdb' AND type='PROCEDURE' and name='procedurename'; – GregW Nov 21 '11 at 20:47
@GregW that query from `mysql.proc` returns blobs for `param_list`, `returns`, and `body`... I'm unable to read them... How did you do it? – Dmitry Efimenko Oct 07 '15 at 22:28
nvm, found answer [here](http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql) – Dmitry Efimenko Oct 07 '15 at 22:30
2

Be aware the ROUTINE_DEFINITION will be null if the user running the query is not the DEFINER and the security is set to DEFINER. You won't have a problem if security is set to INVOKER. You can also get the parameters from `SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_SCHEMA='$dbName' AND SPECIFIC_NAME=\"{$row['ROUTINE_NAME']}\" AND NOT PARAMETER_MODE IS NULL ORDER BY ORDINAL_POSITION;` – Peter Brand Jan 29 '16 at 14:04
Correction, even if security is set to INVOKER, the definition will be null if the user running the query is not the same as the definer. – Peter Brand Jan 29 '16 at 15:17
@GregW mysql 8 does not contain mysq.proc table. any idea how to get the full definition on mysql 8 – – Nasik Ahd Nov 19 '20 at 05:39

score 16 · Answer 3 · edited Oct 30 '15 at 18:20

16

SHOW CREATE PROCEDURE proc_name;

returns the definition of proc_name

edited Oct 30 '15 at 18:20

Ram

3,092
10
40
56

answered Dec 28 '09 at 10:18

valli

5,797
2
19
9

1

This does not show the text of the procedure either. – posfan12 Jun 05 '18 at 23:10
Works for me. I'm using AWS/RDS serverless. Proc text shows up the the Create Procedure column. – KingAndrew Jan 05 '21 at 14:35

score 11 · Answer 4 · edited Dec 01 '15 at 05:51

11

If you want to know the list of procedures you can run the following command -

show procedure status;

It will give you the list of procedures and their definers Then you can run the show create procedure <procedurename>;

edited Dec 01 '15 at 05:51

Manjula

4,961
3
28
41

answered Jun 26 '12 at 19:00

Sibashish Pujari

111
1
2

score 8 · Answer 5 · answered May 15 '18 at 07:55

8

You can use table proc in database mysql:

mysql> SELECT body FROM mysql.proc
WHERE db = 'yourdb' AND name = 'procedurename' ;

Note that you must have a grant for select to mysql.proc:

mysql> GRANT SELECT ON mysql.proc TO 'youruser'@'yourhost' IDENTIFIED BY 'yourpass' ;

answered May 15 '18 at 07:55

Omidreza Bagheri

781
1
11
22

2

Please note that this does not work with MySQL version 8.0, see [bugs.mysql.com/bug.php?id=93814](https://bugs.mysql.com/bug.php?id=93814) – Víctor Gil Mar 26 '21 at 16:38
It's returning as BLOB – Hary Sep 09 '21 at 10:37
1

@Hary Yes, you're right. The 'body' field is longblob, but the output is displayed as text that is the function/store procedure definition. – Omidreza Bagheri Sep 20 '21 at 14:49

score 5 · Answer 6 · answered Dec 28 '09 at 10:29

something like:

DELIMITER //

CREATE PROCEDURE alluser()
BEGIN
   SELECT *
   FROM users;
END //

DELIMITER ;

than:

SHOW CREATE PROCEDURE alluser

gives result:

'alluser', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER', 'CREATE DEFINER=`root`@`localhost` PROCEDURE `alluser`()
BEGIN
   SELECT *
   FROM users;
END'

score 5 · Answer 7 · answered Apr 05 '17 at 10:01

An alternative quick and hacky solution if you want to get an overview of all the produres there are, or run into the issue of only getting the procedure header shown by SHOW CREATE PROCEDURE:

mysqldump --user=<user> -p --no-data --routines <database>

It will export the table descriptions as well, but no data. Works well for sniffing around unknown or forgotten schemas... ;)

score -2 · Answer 8 · edited Sep 21 '17 at 21:59

-2

Perfect, try it:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

edited Sep 21 '17 at 21:59

Rodolfo Luna

829
9
19

answered Aug 05 '15 at 08:35

Sumit

33

3

Welcome to SE! Please explain your answer, especially with regards to what differentiates it from the other existing ones. – anderas Aug 05 '15 at 08:39

View stored procedure/function definition in MySQL

8 Answers8

Linked

Related