oracle 数据字典画报,Oracle 数据字典查询

1.查询表的相关信息

查询结果如下：

查询脚本如下：

select tt1.owner,

tt1.table_name,

tt1.comments,

tt1.last_analyzed,

tt1.num_rows,

tt2.colcnt

from (select t1.owner,

t1.table_name,

c1.comments,

t1.last_analyzed,

t1.num_rows

from all_tables t1, ALL_TAB_COMMENTS c1

where t1.owner = c1.owner

and t1.table_name = c1.table_name) tt1,

(select t5.OWNER, t5.TABLE_NAME, count(1) as colcnt

from dba_tab_columns t5

where t5.OWNER not in

('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM', 'DBSNMP',

'APPQOSSYS', 'WMSYS', 'EXFSYS', 'CTXSYS', 'XDB', 'ANONYMOUS',

'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',

'MDSYS', 'OLAPSYS', 'MDDATA', 'SPATIAL_WFS_ADMIN_USR',

'SPATIAL_CSW_ADMIN_USR', 'SYSMAN', 'MGMT_VIEW', 'FLOWS_FILES',

'APEX_PUBLIC_USER', 'APEX_030200', 'OWBSYS', 'OWBSYS_AUDIT',

'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI', 'XS$NULL')

group by t5.OWNER, t5.TABLE_NAME) tt2

where tt1.owner = tt2.owner

and tt1.table_name = tt2.table_name

order by tt1.owner, tt1.table_name

2.查询字段相关信息

查询结果如下：

查询脚本如下：

select ttt1.owner,

ttt1.table_name,

ttt1.column_name,

ttt1.DATA_TYPE,

ttt1.comments,

(case ttt2.constraint_type

when 'P' THEN

'Y'

ELSE

'N'

END) "是否主键",

(case ttt3.constraint_type

when 'R' THEN

'Y'

ELSE

'N'

END) "是否外键",

(case ttt4.constraint_type

when 'U' THEN

'Y'

ELSE

'N'

END) "是否唯一值",

ttt1.NULLABLE

from (select tt1.owner,

tt1.table_name,

tt1.column_name,

tt1.DATA_TYPE,

tt1.COLUMN_ID,

tt2.comments,

tt1.NULLABLE

from (select t2.owner,

t2.table_name,

t2.column_name,

t2.DATA_TYPE,

t2.COLUMN_ID,

t2.NULLABLE

from all_tab_columns t2

where t2.owner not in

('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM',

'DBSNMP', 'APPQOSSYS', 'WMSYS', 'EXFSYS', 'CTXSYS',

'XDB', 'ANONYMOUS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS',

'SI_INFORMTN_SCHEMA', 'MDSYS', 'OLAPSYS', 'MDDATA',

'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',

'SYSMAN', 'MGMT_VIEW', 'FLOWS_FILES',

'APEX_PUBLIC_USER', 'APEX_030200', 'OWBSYS',

'OWBSYS_AUDIT', 'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI',

'XS$NULL')) tt1,

ALL_COL_COMMENTS tt2

where tt1.owner = tt2.owner

and tt1.table_name = tt2.table_name

and tt1.column_name = tt2.column_name) ttt1

left join

(select col.owner, col.table_name, col.column_name, con.constraint_type

from all_constraints con, all_cons_columns col

where con.constraint_name = col.constraint_name

and con.owner = col.owner

and con.table_name = col.table_name

and con.constraint_type = 'P') ttt2 on ttt1.owner = ttt2.owner

and ttt1.table_name =

ttt2.table_name

and ttt1.column_name =

ttt2.column_name

left join

(select col.owner, col.table_name, col.column_name, con.constraint_type

from all_constraints con, all_cons_columns col

where con.constraint_name = col.constraint_name

and con.owner = col.owner

and con.table_name = col.table_name

and con.constraint_type = 'R') ttt3 on ttt1.owner = ttt3.owner

and ttt1.table_name =

ttt3.table_name

and ttt1.column_name =

ttt3.column_name

left join

(select col.owner, col.table_name, col.column_name, con.constraint_type

from all_constraints con, all_cons_columns col

where con.constraint_name = col.constraint_name

and con.owner = col.owner

and con.table_name = col.table_name

and con.constraint_type = 'U') ttt4 on ttt1.owner = ttt4.owner

and ttt1.table_name =

ttt4.table_name

and ttt1.column_name =

ttt4.column_name

order by ttt1.owner, ttt1.table_name, ttt1.COLUMN_ID