Searching for Text in Stored Procedures and Functions in MySQL

While debugging packages and procedures in an Oracle database I have become accustomed to searching through the source of all packages and procedures in the database. To do this in Oracle, one would query the Data Dictionary ALL_SOURCE (or DBA_SOURCE, depending on permissions) like so:

SELECT * 
FROM all_source 
WHERE lower(text) 
        LIKE '%search-string-in-lower-case-here%';

This is generally a quick and dirty way to search all procedures, packages, and functions for something like a custom error string or calls to other procedures (which can also be done with ALL_DEPENDENCIES)

MySQL stores routines a bit differently than Oracle, but this is still possible. Since MySQL does not have dependency tracking functionality, if one makes a change to a stored function and wants to find all other stored procedures that call that function, one would query the PROC table in the MYSQL database like so:

SELECT * 
FROM mysql.proc 
WHERE lower(CONVERT(body using utf8)) 
        LIKE '%stored-function-name-in-lowercase-here%';

Gotchas about this query:

The reason I’m posting this to my blog mainly is because there are two somewhat not-straightforward things about this query that should be noted. First of all, please note that the query converts the body of the routine to lower case. This is done because calls made in the DDL of routines can be made in any case, since SQL syntax is case-insensitive.

With that being said, due to MySQL storing routine’s bodies as a BLOB type, and since the BLOB type is stored in binary format, it is necessary to convert the body to a character set (like UTF8) using something like CONVERT(body using utf8) before using the LOWER() function on it.

For more information about converting BLOB text please see the MySQL bug note about it here.