Show procedure definition in MySQL

You can use this:

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

SHOW CREATE PROCEDURE proc_name; 

returns the definition of proc_name

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>;

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' ; 

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' 

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... ;)

You can show the CREATE PROCEDURE statement with the SQL below. *The doc explains the SQL below and \G can show it more clearly and you must select a database when showing the CREATE PROCEDURE statement otherwise there is the error:

SHOW CREATE PROCEDURE <procedure_name>; 

Or:

SHOW CREATE PROCEDURE <procedure_name>\G 

if you need view/modify procedure using MYSQL workbench GUI you can do it this way

as shown above you can copy or send to editor direct, or alter and drop it.

Perfect, try it:

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