oracle 查询主表的主键被其他表作为外键参照的SQL

1,查询主表的主键被其他表作为外键参照的SQL

select child_cons.constraint_name,

child_cons.table_name child_table_name,

parent_cons.table_name parent_table,

child_cons.status

from (select *

from all_constraints

where owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

and table_name ='&parent_tab') parent_cons,

(select *

from all_constraints

where owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

and constraint_type ='R') child_cons

where parent_cons.constraint_name = child_cons.r_constraint_name;

 

2,Disable 其他表的外键约束

 

 

select'alter table ' || child_cons.table_name ||' disable constraint ' ||

child_cons.constraint_name || ';'

 

from (select *

from all_constraints

where owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

and table_name ='&parent_tab') parent_cons,

(select *

from all_constraints

where owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

and constraint_type ='R') child_cons

where parent_cons.constraint_name = child_cons.r_constraint_name;