Find the table dependent on procedure/function in MySQL/MariaDB

Check Tables / Views dependent on Procedure/Function in MySQL

Check the list of tables dependent on Procedure/Function in MySQL or MariaDB.

SELECT a.routine_name,b.table_name,a.routine_schema,a.routine_type
FROM information_schema.ROUTINES  a
INNER JOIN (SELECT table_name , table_schema
            FROM information_schema.tables 
            ) b
    ON a.ROUTINE_DEFINITION LIKE concat('%',b.table_name,'%') 
	where  b.table_schema = 'dbname'  and  a.ROUTINE_NAME = 'myprocedure';

Check the view dependence on Procedure/Function in MySQL or MariaDB

SELECT a.routine_name,b.table_name,a.routine_schema,a.routine_type
FROM information_schema.ROUTINES  a
INNER JOIN (SELECT table_name , table_schema
            FROM information_schema.views  
            ) b
    ON a.ROUTINE_DEFINITION LIKE concat('%',b.table_name,'%') 
	where  b.table_schema = 'dbname'  and  a.ROUTINE_NAME = 'myprocedure'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.