欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

一个SQL查询导出数据字典

程序员文章站 2022-07-02 13:55:23
/*SELECT COUNT(*) FROM DICT_ZGY_201808;--135430SELECT * FROM DICT_ZGY_201808_5;SELECT T.USERNAME,T.CREATED FROM DBA_USERS T WHERE T.ACCOUNT_STATUS='OP ......

/*
SELECT COUNT(*) FROM DICT_ZGY_201808;--135430
SELECT * FROM DICT_ZGY_201808_5;
SELECT T.USERNAME,T.CREATED FROM DBA_USERS T WHERE T.ACCOUNT_STATUS='OPEN' ORDER BY 1 ASC ;

SELECT DISTINCT T.username FROM DBA_USERS T ;
*/

CREATE TABLE DICT_ZGY_201808_1 AS

SELECT S.OWNER,
S.TABLE_NAME,
S.COLUMN_NAME,
S.COLUMN_ID,
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',
'SCOTT',
'HR',
'SH',
'TEST1',
'TESTQRY',
'TEST',)
ORDER BY S.OWNER, S.TABLE_NAME, S.COLUMN_ID;