List stored procedures in MySQL

List stored procedures in MySQL

Posted in MySql - Last updated Oct. 05, 2011

This post shows how to get a complete list of stored procedures in a MySQL database and then to see what code is used in the stored procedure.

List MySQL stored procedures

Run the following SQL query either from the MySQL command line, or using a GUI tool like phpMyAdmin to get a complete list of stored procedures from all databases your login has access to:

SHOW PROCEDURE STATUS

To just list procedures from a particular database do this, where we want to query stored procedures from the "mydb_abc" database:

SHOW PROCEDURE STATUS WHERE Db = 'mydb'

The output from the above commands will look something like this:

+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name    | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mydb | mysproc | PROCEDURE | root@%  | 2011-08-18 20:29:53 | 2011-08-18 20:29:53 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

Show the SQL code in the stored procedure

Use the show create procedure query to get the SQL code from the query. To get it for the "mysproc" stored procedure in the example output above, do this:

SHOW CREATE PROCEDURE mysproc

This then gives you a few fields as a resultset and it's the "Create Procedure" column which has the procedure creation SQL.