Ravikumar_n Ravikumar_n - 4 months ago 7
SQL Question

How to find a table is used in any procedure or function or view

I am using MySQL, in my database I have a table named

tbltest
.

I want to check where this table is used in my database.

For an example: I want to check if this table is used in any store procedure/function/view.

How can i check in MySQL.

Answer

As per the MySQL documentation

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbltest'

In case, if you want to check only in stored procedure/ function / view, then you can join with INFORMATION_SCHEMA.ROUTINES

UPDATE:

ROUTINE_TYPE will provide the type like procedure/function.

SELECT DISTINCT ROUTINE_NAME, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tbltest%'
      -- AND ROUTINE_TYPE = 'PROCEDURE' -- to filter SPs only