MySQL Server
MySQL Server

MYSQL request for all Stored Procedures that call a certain table

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.

onaled777Asked: 2015-07-24

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 62

gheistCommented: 2015-07-25

stored procedures are programs, so MySQL has no clue what is in them until executed.

LVL 51

PortletPaulEE Topic AdvisorCommented: 2015-07-25

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

LVL 52

Steve BinkCommented: 2015-07-25

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial