List MySQL Stored Procedures

When using stored procedures with MySQL you may at some point want to get a list of all the stored procedures that exist in your database. Using the MySQL INFORMATION_SCHEMA  database and the ROUTINES table we can easily list information about procedures or functions.

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'brightfunction'
AND ROUTINE_TYPE = 'PROCEDURE'
;

This example will list the ROUTINE_NAME for all stored procedures in the database brightfuncion.

Slow INFORMATION_SCHEMA.ROUTINES Queries

If you have a lot of stored procedures you may find that queries on the ROUTINES table start to take longer. Inspecting the table there are not any indexes so it is no surprise that queries get slower once there is more data in the table. As the table is a system table used by MySQL we do not want to think about making any changes to it.

An alternative to the ROUTINES table is to use the mysql.proc table which stores all the information for routines and has indexes on the database, name and type of the routine which makes queries a lot faster:

SELECT name
FROM mysql.proc
WHERE db = 'brightfunction'
AND type = 'procedure'
;

The stored procedure definition can also be queried allowing you to find particular SQL statements:

SELECT name, body_utf8
FROM mysql.proc
WHERE db = 'brightfunction'
AND type = 'procedure'
and body_utf8 like '%delete from%'
;

Care should be taken when accessing both of these tables as they are used by MySQL internally; the table’s schema and data should not be edited.