一条SQL生成数据字典
有个字典表并定期维护，对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;
