-- 查询外键约束(某表的所有父表) select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name from user_constraints c,user_cons_columns cc,user_cons_columns rcc where c.owner='SALIEN_SBGL' and c.table_name='JH_NDGXLGJH_TB' and c.constraint_type='R' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name and cc.position=rcc.position order by c.constraint_name,cc.position;--查询连接到某表的所有外键(某表的所有子表) select rcc.owner,rcc.table_name,rcc.constraint_name,rcc.column_name,c.table_name,c.constraint_name,cc.column_name from user_constraints c,user_cons_columns cc,user_cons_columns rcc where lower(c.owner)='d7i' and rcc.table_name='R5HOME' and c.constraint_type='R' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name and cc.position=rcc.position order by c.constraint_name,cc.position-- 查询主键唯一键约束 select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner='SALIEN_SBGL' and c.table_name='JH_NDGXLGJH_TB' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.constraint_type in ('P','U') order by c.constraint_type,c.constraint_name,cc.position;
查询 ALL_CONSTRAINTS.
先 desc ALL_CONSTRAINTS,然后根据你的需要选择查询列。
-- 查询外键约束(某表的所有父表)
select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name
from user_constraints c,user_cons_columns cc,user_cons_columns rcc
where c.owner='SALIEN_SBGL'
and c.table_name='JH_NDGXLGJH_TB'
and c.constraint_type='R'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.r_owner=rcc.owner
and c.r_constraint_name=rcc.constraint_name
and cc.position=rcc.position
order by c.constraint_name,cc.position;--查询连接到某表的所有外键(某表的所有子表)
select rcc.owner,rcc.table_name,rcc.constraint_name,rcc.column_name,c.table_name,c.constraint_name,cc.column_name
from user_constraints c,user_cons_columns cc,user_cons_columns rcc
where lower(c.owner)='d7i'
and rcc.table_name='R5HOME'
and c.constraint_type='R'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.r_owner=rcc.owner
and c.r_constraint_name=rcc.constraint_name
and cc.position=rcc.position
order by c.constraint_name,cc.position-- 查询主键唯一键约束
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner='SALIEN_SBGL'
and c.table_name='JH_NDGXLGJH_TB'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.constraint_type in ('P','U')
order by c.constraint_type,c.constraint_name,cc.position;
不过需要安装Oracle的客户端。