[SQL]
SELECT *
FROM
(
SELECT A.OWNER
, A.TABLE_NAME
, B.COMMENTS AS TABLE_NAME_CO
, C.COLUMN_ID
, C.COLUMN_NAME
, D.COMMENTS AS COLUMN_NAME_CO
, C.DATA_TYPE || CASE WHEN C.DATA_TYPE IN ('VARCHAR2','CHAR') THEN ' (' || C.CHAR_LENGTH || ' Byte)'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE > 0 THEN ' (' || C.DATA_PRECISION ||','|| C.DATA_SCALE ||')'
WHEN C.DATA_TYPE = 'NUMBER' AND C.DATA_SCALE = 0 THEN ' (' || C.DATA_PRECISION ||')'
ELSE C.DATA_TYPE END AS DATA_TYPE
, C.NULLABLE
, (SELECT POSITION
FROM ALL_CONS_COLUMNS Y
, ALL_CONSTRAINTS Z
WHERE Y.OWNER = Z.OWNER
AND Y.TABLE_NAME = Z.TABLE_NAME
AND Y.CONSTRAINT_NAME = Z.CONSTRAINT_NAME
AND Z.CONSTRAINT_TYPE = 'P'
AND Y.OWNER = A.OWNER
AND Y.TABLE_NAME = A.TABLE_NAME
AND Y.COLUMN_NAME = C.COLUMN_NAME) AS COL_PK
, C.DATA_DEFAULT AS COL_DEFAULT
FROM ALL_TABLES A
, ALL_TAB_COMMENTS B
, ALL_TAB_COLUMNS C
, ALL_COL_COMMENTS D
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND C.OWNER = A.OWNER
AND C.TABLE_NAME = A.TABLE_NAME
AND D.OWNER = A.OWNER
AND D.TABLE_NAME = A.TABLE_NAME
AND D.COLUMN_NAME = C.COLUMN_NAME
)
WHERE OWNER LIKE '%'
AND TABLE_NAME LIKE '%'
ORDER BY TABLE_NAME, COLUMN_ID
|
엑셀 문서작업 한다고 필요해서 작업했던 것인데 나중에 또 써먹을 것 같아서 보관...
