Mysql: Find all stored procedures that reference a specific table

Question

I want to check all SPs/function that reference a particular table in mysql. I found a query which I belive is to check the same in sql server:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tablename%'

But in mysql it says 'Table sys.procedures doesn't exist'

Possible duplicate of [List of Stored Procedures/Functions Mysql Command Line](https://stackoverflow.com/questions/733349/list-of-stored-procedures-functions-mysql-command-line) — Raptor, Aug 02 '17 at 08:05

score 10 · Answer 1 · answered Jan 23 '19 at 22:16

10

select * from information_schema.ROUTINES where ROUTINE_DEFINITION like '%tableName%';

answered Jan 23 '19 at 22:16

Nickhil

1,267
10
11

score 4 · Answer 2 · answered Aug 02 '17 at 08:07

You need to query Mysql.proc table, here's the documentation:

The mysql.proc table contains information about stored procedures and stored functions. It contains similar information to that stored in the INFORMATION SCHEMA.ROUTINES table.

Your query would be:

SELECT *
FROM Mysql.proc
WHERE type = 'PROCEDURE'
AND body LIKE '%tablename%';

Mysql: Find all stored procedures that reference a specific table

2 Answers2