MYSQL request for all Stored Procedures that call a certain table

onaled777 used Ask the Experts™
I need to see if I can wipe a table without breaking other stored procedures.

In MySQL is it possible to query the system to find all Stored Procedures that reference a certain table.  

Sorry I do not know the version on MySQl that is being used.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
stored procedures are programs, so MySQL has no clue what is in them until executed.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
You may need to learn what version you are using.

It is possible to get a list of stored procs:

(nb: I believe this could be used to create a set of show procedure code statements dynamically)

Then it is possible to display the code of each stored proc.

     SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE CODE was added in MySQL 5.0.17.
However, I'm not sure how to search through this output to suit your specific need
Top Expert 2004
This isn't something you can do with 100% accuracy, but this query will narrow it down for you:
select db,name,type from mysql.proc where body like '%MyTableName%';

Open in new window

Just replace "MyTableName" with the name of the table.  Note that this is a text search through the body of the procedure, so if the table name appears in comments, or even as a substring of another identifier, it will show in the return.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial