전체 테이블, 칼럼 내용 출력 (PK, FK 여부 포함)
DATABASE/ORACLE 2013. 10. 8. 18:09--테이블(논리)명 테이블(물리)명 컬럼(논리)명 컬럼(물리)명 TYPE&LENGTH NULL PK FK
SELECT B.COMMENTS, T.TABLE_NAME, C.COMMENTS, T.COLUMN_NAME, T.DATA_TYPE
|| '('|| CASE WHEN T.DATA_PRECISION IS NULL THEN T.DATA_LENGTH ELSE T.DATA_PRECISION END ||')'
, CASE WHEN T.NULLABLE = 'Y' THEN 'NULL' ELSE 'NOT NULL' END
, CASE WHEN E.COLUMN_NAME IS NOT NULL THEN 'PK' END PK
, CASE WHEN F.COLUMN_NAME IS NOT NULL THEN 'FK' END FK
FROM USER_TAB_COLUMNS T
LEFT OUTER JOIN USER_TAB_COMMENTS B
ON B.TABLE_NAME = T.TABLE_NAME
LEFT OUTER JOIN USER_COL_COMMENTS C
ON C.TABLE_NAME = T.TABLE_NAME
AND C.COLUMN_NAME = T.COLUMN_NAME
LEFT OUTER JOIN
(SELECT B.TABLE_NAME, B.COLUMN_NAME
FROM USER_CONSTRAINTS A
INNER JOIN USER_CONS_COLUMNS B
ON B.TABLE_NAME = A.TABLE_NAME
WHERE CONSTRAINT_TYPE = 'P'
GROUP BY B.TABLE_NAME, B.COLUMN_NAME
) E ON E.TABLE_NAME = T.TABLE_NAME AND E.COLUMN_NAME = T.COLUMN_NAME
LEFT OUTER JOIN
(SELECT B.TABLE_NAME, B.COLUMN_NAME
FROM USER_CONSTRAINTS A
INNER JOIN USER_CONS_COLUMNS B
ON B.TABLE_NAME = A.TABLE_NAME
WHERE CONSTRAINT_TYPE = 'F'
GROUP BY B.TABLE_NAME, B.COLUMN_NAME
) F ON F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_NAME = T.COLUMN_NAME
ORDER BY T.TABLE_NAME, T.COLUMN_ID
;
