![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
SELECT ATC.TABLE_NAME,
ATC.COLUMN_NAME,
DECODE(ATC.DATA_TYPE,
'NUMBER',
ATC.DATA_TYPE || '(' || ATC.DATA_PRECISION || ',' ||
ATC.DATA_SCALE || ')',
'VARCHAR2',
ATC.DATA_TYPE || '(' || ATC.DATA_LENGTH || ')',
ATC.DATA_TYPE),
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**/ /**//**/ /**//**/ /**//* ATC. DATA_TYPE,
ATC. DATA_LENGTH 长度,
ATC. DATA_PRECISION 整数位,
ATC. DATA_SCALE 小数位,*/
ATC. NULLABLE "NULLABLE",
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**/ /**//**/ /**//**/ /**//*ATC. DATA_DEFAULT ,*/
DECODE(POSITION, NULL, '', 'Y') "PRIMARY KEY",
PRIMARY_SUB.POSITION "PKEY ORDER",
-- FOREIGN_SUB.fk_owner ,
FOREIGN_SUB.fk_table_name,
FOREIGN_SUB.fk_column_name,
ACCOM. COMMENTS
FROM ALL_TAB_COLUMNS ATC,
ALL_COL_COMMENTS ACCOM,
(SELECT ACC.OWNER, ACC.TABLE_NAME, ACC.COLUMN_NAME, ACC.POSITION
FROM ALL_CONS_COLUMNS ACC, ALL_CONSTRAINTS AC
WHERE AC.OWNER = ACC.OWNER
AND AC.TABLE_NAME = ACC.TABLE_NAME
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND ACC.OWNER = 'ELS'
-- AND ACC.TABLE_NAME = 'LMS_EVENT_PARTS'
AND AC.CONSTRAINT_TYPE = 'P') PRIMARY_SUB,
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
(select /**//*c.constraint_name,*/
CC.OWNER pk_owner,
CC.TABLE_NAME pk_table_name, --primary table :cc; foreign table rcc
cc.column_name pk_column_name,
rcc.owner fk_owner,
rcc.table_name fk_table_name,
rcc.column_name fk_column_name
from ALL_constraints c, ALL_cons_columns cc, ALL_cons_columns rcc
where c.owner = 'ELS'
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* and c.table_name = 'EIS_USER_PROJECTS'*/
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) FOREIGN_SUB
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE ATC.TABLE_NAME = ACCOM.TABLE_NAME
AND ATC.OWNER = ACCOM.OWNER
AND ATC.COLUMN_NAME = ACCOM.COLUMN_NAME
--
AND ATC.TABLE_NAME = PRIMARY_SUB.TABLE_NAME(+)
AND ATC.OWNER = PRIMARY_SUB.OWNER(+)
AND ATC.COLUMN_NAME = PRIMARY_SUB.COLUMN_NAME(+)
--
AND ATC.TABLE_NAME = FOREIGN_SUB.PK_TABLE_NAME(+)
AND ATC.OWNER = FOREIGN_SUB.PK_OWNER(+)
AND ATC.COLUMN_NAME = FOREIGN_SUB.PK_COLUMN_NAME(+)
--
AND ATC.OWNER = 'ELS'
--AND ATC.TABLE_NAME = 'EIS_USER_PROJECTS' --
ORDER BY ATC.OWNER,
ATC.TABLE_NAME,
PRIMARY_SUB.POSITION,
FOREIGN_SUB.fk_table_name,
ATC.COLUMN_NAME
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
-- 查询外键约束(查某表的所有父表)
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;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
约束的类型有如下几种:
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)