Mysql Command to view the definition of stored procedure ?

Question

Mysql command to view the definition of stored procedure . I know sp_helptext is the command in MS-Sql to display the definition of stored procedure , is there a similar command in Mysql ?

I am not looking for SHOW PROCEDURE STATUS which display the list of the procedures available for the particular database in mysql . Thanks !

Answer 1

SHOW CREATE PROCEDURE

Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement

Answer 2

SHOW CREATE PROCEDURE proc_name; returns the definition of proc_name

Answer 3

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'

Answer 4

u can use this :

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