整理oracle数据字典,Oracle数据库之一条SQL生成数据字典

本文主要向大家介绍了Oracle数据库之一条SQL生成数据字典，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

有个字典表并定期维护，对DBA和开发很重要，终于把他们整合在一起了，看有没问题？

一条SQL生成数据字典，包含所有OPEN用户、表名、字段名、字段序号、字段属性、默认值、是否非空、字段意思、主键标识、外键标识、主键表名、主键字段名、外键表名、外键字段名、外键名、外键标识、外键表用户

其中联合外键会出现字段列出现多行，TIMESTAMP(6)(,6)需要手工处理

CREATE TABLE DICT_ZGY_20180814 AS

SELECT S.OWNER,

S.TABLE_NAME,

S.COLUMN_ID,

S.COLUMN_NAME,

S.COLTYPE,

TO_LOB(S.DEFAULTVAL) AS DEFAULTVAL,

S.NULLYN,

S.COMMENTSS,

CASE

WHEN PK.COLUMN_POSITION > 0 THEN

'√'

ELSE

''

END AS PKYN,

CASE

WHEN FK.CONSTRAINT_TYPE = 'R' THEN

'√'

ELSE

''

END AS FKYN,

FK.*

FROM (SELECT A.OWNER,

A.TABLE_NAME,

A.COLUMN_NAME,

A.COLUMN_ID,

DECODE(A.CHAR_LENGTH,

0,

DECODE(A.DATA_SCALE,

NULL,

A.DATA_TYPE,

A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' ||

A.DATA_SCALE || ')'),

A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')') AS COLTYPE,

A.DATA_DEFAULT AS DEFAULTVAL,

CASE

WHEN A.NULLABLE = 'Y' THEN

'√'

ELSE

''

END AS NULLYN,

B.COMMENTS AS COMMENTSS

FROM SYS.ALL_TAB_COLUMNS A, SYS.DBA_COL_COMMENTS B

WHERE A.OWNER = B.OWNER

AND A.TABLE_NAME = B.TABLE_NAME

AND A.COLUMN_NAME = B.COLUMN_NAME) S,

(SELECT AA.INDEX_OWNER,

AA.TABLE_NAME,

AA.COLUMN_POSITION,

AA.COLUMN_NAME

FROM ALL_IND_COLUMNS AA, ALL_CONSTRAINTS BB

WHERE BB.CONSTRAINT_TYPE = 'P'

AND AA.TABLE_NAME = BB.TABLE_NAME

AND AA.INDEX_NAME = BB.CONSTRAINT_NAME

AND AA.INDEX_OWNER = BB.OWNER) PK,

(SELECT BB2.TABLE_NAME PTABLE_NAME,

BB2.COLUMN_NAME PCOLUMN_NAME,

AA1.TABLE_NAME FTABLE_NAME,

AA1.COLUMN_NAME FCOLUMN_NAME,

AA1.CONSTRAINT_NAME,

AA1.CONSTRAINT_TYPE,

AA1.OWNER FOWNER

FROM (SELECT A1.CONSTRAINT_NAME,

B1.TABLE_NAME,

B1.COLUMN_NAME,

A1.R_CONSTRAINT_NAME,

A1.CONSTRAINT_TYPE,

A1.OWNER

FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS B1

WHERE A1.CONSTRAINT_TYPE = 'R'

AND A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME

AND A1.OWNER = B1.OWNER) AA1,

(SELECT DISTINCT A2.R_CONSTRAINT_NAME,

B2.TABLE_NAME,

B2.COLUMN_NAME

FROM ALL_CONSTRAINTS A2, ALL_CONS_COLUMNS B2

WHERE A2.CONSTRAINT_TYPE = 'R'

AND A2.R_CONSTRAINT_NAME = B2.CONSTRAINT_NAME) BB2

WHERE AA1.R_CONSTRAINT_NAME = BB2.R_CONSTRAINT_NAME) FK,

DBA_USERS U

WHERE S.OWNER = PK.INDEX_OWNER(+)

AND S.TABLE_NAME = PK.TABLE_NAME(+)

AND S.COLUMN_NAME = PK.COLUMN_NAME(+)

AND S.OWNER = FK.FOWNER(+)

AND S.TABLE_NAME = FK.FTABLE_NAME(+)

AND S.COLUMN_NAME = FK.FCOLUMN_NAME(+)

AND S.OWNER = U.USERNAME(+)

AND U.ACCOUNT_STATUS = 'OPEN'

AND U.USERNAME NOT IN ('SYS', 'SYSTEM')

ORDER BY S.OWNER, S.TABLE_NAME, S.COLUMN_ID;

查询索引字段,并导出字典表成xlsx格式

SELECT T.TABLE_OWNER,

T.TABLE_NAME,

T.INDEX_OWNER,

T.INDEX_NAME,

T.COLUMN_NAME,

T.COLUMN_POSITION

FROM ALL_IND_COLUMNS T, DBA_USERS U

WHERE T.INDEX_OWNER = U.USERNAME

AND U.ACCOUNT_STATUS = 'OPEN'

AND U.USERNAME NOT IN ('SYS', 'SYSTEM')

ORDER BY T.TABLE_OWNER, T.TABLE_NAME, T.INDEX_NAME, T.COLUMN_POSITION;

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！