一、查询表主键列

select column_name 
from dba_cons_columns 
where constraint_name in (select constraint_name 
from dba_constraints 
where table_name = upper('tablename') 
and constraint_type = 'P');

二、查询表外键列

select column_name 
from dba_cons_columns 
where constraint_name in (select constraint_name 
from dba_constraints 
where table_name = upper('tablename') 
and constraint_type = 'R');

三、查询表中列

select column_name 
from dba_tab_columns 
where table_name = upper('tablename');

四、查询表之间的主外键关系

select b.table_name 主键表名, 
b.column_name 主键列名, 
a.table_name 外键表名, 
a.column_name 外键列名 
from (select a.constraint_name, 
b.table_name, 
b.column_name, 
a.r_constraint_name 
from dba_constraints a, dba_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 dba_constraints a, dba_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;

[@more@]

ITPub博客

查询oracle中主外键列

最新文章