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

User · Answer

You can use this   SELECT ROUTINE DEFINITION FROM INFORMATION SCHEMA ROUTINES WHERE ROUTINE SCHEMA    yourdb  AND ROUTINE TYPE    PROCEDURE  AND ROUTINE NAME    procedurename

User · Answer

SHOW CREATE PROCEDURE proc name    returns the definition of proc name

User · Answer

You can use table proc in database mysql   mysql gt  SELECT body FROM mysql proc WHERE db    yourdb  AND name    procedurename      Note that you must have a grant for select to mysql proc   mysql gt  GRANT SELECT ON mysql proc TO  youruser   yourhost  IDENTIFIED BY  yourpass

User · Answer

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

User · Answer

Perfect  try it   SELECT ROUTINE DEFINITION FROM INFORMATION SCHEMA ROUTINES     WHERE ROUTINE SCHEMA    yourdb  AND ROUTINE TYPE    PROCEDURE  AND ROUTINE NAME    procedurename

User · Answer

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  lt procedurename gt

User · Answer

SHOW CREATE PROCEDURE  lt name gt    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

User · Answer

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  lt user gt  -p --no-data --routines  lt database gt    It will export the table descriptions as well  but no data  Works well for sniffing around unknown or forgotten schemas

[mysql] View stored procedure/function definition in MySQL

Examples related to mysql

Examples related to stored-procedures

Examples related to stored-functions