Query to find the dependent object on Table in MySQL or MariaDB

Find the dependence on Table in MYSQL / MariaDB

Check the Procedures and functions depending on the table:

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 b.table_name = 'tablename' ;

Check the views dependence on the table.

SELECT *
FROM information_schema.views 
WHERE Table_Schema='dbname' 
  AND Table_Name = 'tablename';

Check the foreign key constraints depend on the table.

SELECT 
     Constraint_Type
    ,Constraint_Name
    ,Table_Schema
    ,Table_Name
FROM information_schema.table_constraints
WHERE Table_Schema ='dbname' AND Table_Name = 'tablename' and Constraint_Type = 'FOREIGN KEY';

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.