/*
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;