CODEWITHSUNDEEP
mysql
Lisen
Lisen

Reputation: 188

MySQL: How to list all tables that are used in a procedure?

I'm looking for a method or a query to retrieve all tables that are used in a procedure.
I tried information_schema.routinesbut it contains all the definition for a procedure.
Is there any system table that contains the dependency relationship for this ?
Or how can I get table names from the definitions using other language such as Python?
Thanks a lot!!

Upvotes: 0

Views: 356

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562378

The current version of MySQL does not implement such a view in INFORMATION_SCHEMA.

MySQL 8.0.13 added I_S.VIEW_TABLE_USAGE, which allows you to look up the tables used by a view. This was done for WorkLog #11864. That WorkLog notes compatibility with PostgreSQL and Microsoft SQL Server.

However, there is no WorkLog I can find for an hypothetical I_S.ROUTINE_TABLE_USAGE table. I checked PostgreSQL, and it has this view: https://www.postgresql.org/docs/current/infoschema-routine-table-usage.html but MySQL does not.

So to get this information automatically, you would have to query the procedure body, and parse it for table references. Not an easy task.

Upvotes: 1

Related Questions