Thursday, August 7, 2008

Useful SQLs

Child tables not having index on foreign key columns
select table_name,column_name from user_cons_columns where constraint_name in (select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table_name') and constraint_type='R') minus (select table_name,column_name from user_ind_columns);



child tables of a parent table

select table_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table name');