Find all MySQL Stored Procedure calls?

Question

For a given MySQL DB that I use and modify occasionally I had to recently make some changes to some tables and stored procedures. There are places in this DB where procedures make calls to other procedures. I found the task of hunting down everywhere that I needed to modify the parameters to these modified procedures a hassle and resorted to dumping the DB and doing a text search on the dumped file to find all the CALL statements.

I'm wondering if there's a better and easier way to do this. I did some cursory searching here on SO and also googled for a solution but really didn't find an answer. I suspect that there's a way to come up with a list of the calls made or some utility out there that makes it simple, but I haven't found this solution.

Any ideas?

Answer 1

Well, I finally stumbled upon the following solution:

The INFORMATION_SCHEMA.ROUTINES table has information that can be very useful when trying to track down CALLs from one SP to another. I used the following:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE "%SomeProc%";

And this retrieved all the store procedures that contained SomeProc.

Answer 2

Wow this is awesome! I'm using this to search for text in my MySQL database:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE "%search_string%";