oracle 主外键查询,查询oracle中主外键列 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 ...

一、查询表主键列

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;