select fk_col.constraint_name, fk_col.table_name, fk_col.column_name
from user_constraints pk, user_constraints fk, user_cons_columns fk_col
where pk.table_name = 'TEST'
and pk.constraint_type = 'P'
and fk.r_constraint_name = pk.constraint_name
and fk_col.constraint_name = fk.constraint_name
order by 2, 3;
select a.table_name 外键表名,a.column_name 外键列名,b.table_name 主键表名,b.column_name 主键列名
from
(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and a.constraint_name=b.constraint_name
) a,
(select distinct a.r_constraint_name,b.table_name,b.column_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and
a.r_constraint_name=b.constraint_name)
b
from user_constraints pk, user_constraints fk, user_cons_columns fk_col
where pk.table_name = 'TEST'
and pk.constraint_type = 'P'
and fk.r_constraint_name = pk.constraint_name
and fk_col.constraint_name = fk.constraint_name
order by 2, 3;
select a.table_name 外键表名,a.column_name 外键列名,b.table_name 主键表名,b.column_name 主键列名
from
(select a.constraint_name,b.table_name,b.column_name,a.r_constraint_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and a.constraint_name=b.constraint_name
) a,
(select distinct a.r_constraint_name,b.table_name,b.column_name
from user_constraints a, user_cons_columns b
WHERE a.constraint_type='R'
and
a.r_constraint_name=b.constraint_name)
b
where a.r_constraint_name=b.r_constraint_name
转自:http://www.blogjava.net/leekiang/archive/2007/08/13/136345.html