[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
|
엑셀 문서작업 한다고 필요해서 작업했던 것인데 나중에 또 써먹을 것 같아서 보관...