--테이블 목록
SELECT * FROM USER_TAB_COMMENTS ORDER BY TABLE_NAME ASC;
--테이블 상세 조회
SELECT
B.TABLE_NAME,
B.COLUMN_ID AS 컬럼순서,
B.COLUMN_NAME AS 컬럼명,
A.COMMENTS AS 설명,
B.DATA_TYPE AS 테이터타입,
B.DATA_LENGTH AS 길이,
B.NULLABLE AS NULL구분
FROM USER_COL_COMMENTS A, USER_TAB_COLUMNS B, USER_TAB_COMMENTS C
WHERE B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME
AND A.TABLE_NAME = C.TABLE_NAME
ORDER BY B.TABLE_NAME, B.COLUMN_ID
--테이블 상세 조회 (PK 구분 포함)
SELECT
AA.TABLE_NAME,
AA.COLUMN_ID AS 컬럼순서,
AA.COLUMN_NAME AS 컬럼명,
AA.COMMENTS AS 설명,
AA.DATA_TYPE AS 테이터타입,
AA.DATA_LENGTH AS 길이,
AA.NULLABLE AS NULL구분,
BB.PK AS 키타입
FROM (
SELECT
B.TABLE_NAME,
B.COLUMN_ID,
B.COLUMN_NAME,
A.COMMENTS,
B.DATA_TYPE,
B.DATA_LENGTH,
B.NULLABLE
FROM USER_COL_COMMENTS A, USER_TAB_COLUMNS B, USER_TAB_COMMENTS C
WHERE B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME
AND A.TABLE_NAME = C.TABLE_NAME
) AA, (
SELECT A.TABLE_NAME, B.COLUMN_NAME, 'PK' AS PK
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'P'
) BB
WHERE AA.TABLE_NAME = BB.TABLE_NAME(+)
AND AA.COLUMN_NAME = BB.COLUMN_NAME(+)
ORDER BY AA.TABLE_NAME, AA.COLUMN_ID