查询oracle中主外键列

一、查询表主键列

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;