Pages

Tuesday, April 19, 2022

Find the dependence on Table in MYSQL / 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 = 'test_table' ;
Check the views dependence on the table.
select * from information_schema.views where table_schema='mqm1' and table_name = 'test_table';
  
  
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';

No comments:

Post a Comment