MYSQL request for all Stored Procedures that call a certain table

onaled777
onaled777 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

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

It is possible to get a list of stored procs:

      SHOW PROCEDURE STATUS
(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
Commented:
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