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

Question

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 1

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

To check in VIEWS:

SELECT DISTINCT TABLE_NAME AS Name, 'VIEW' AS Type 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION LIKE '%view_name%'