테이블 칼럼, 타입, 설명 조회

 

/**********************************************************************/
-- 테이블 스키마 정보
/**********************************************************************/
SELECT
    A.COLUMN_NAME
    , B.COMMENTS
    , A.DATA_TYPE
    , A.DATA_LENGTH
    , CASE WHEN C.CONSTRAINT_TYPE = 'P' THEN '○' ELSE '' END    -- PK 여부
    , CASE WHEN A.NULLABLE = 'N' THEN '○' ELSE '' END

    , A.DATA_DEFAULT
FROM USER_TAB_COLUMNS A, USER_COL_COMMENTS B, (
                                                SELECT
                                                    C.TABLE_NAME
                                                    , C.COLUMN_NAME
                                                    , D.CONSTRAINT_TYPE
                                                FROM USER_CONS_COLUMNS C
                                                    , (SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P') D
                                                WHERE C.TABLE_NAME = D.TABLE_NAME
                                                    AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
                                              ) C
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
    AND A.COLUMN_NAME = B.COLUMN_NAME(+)
    AND A.TABLE_NAME = C.TABLE_NAME(+)
    AND A.COLUMN_NAME = C.COLUMN_NAME(+)
    AND A.TABLE_NAME = '테이블명'
ORDER BY COLUMN_ID;

/**********************************************************************/
-- 테이블 인덱스 정보
/**********************************************************************/
SELECT
    A.UNIQUENESS
    , B.INDEX_NAME
    , B.COLUMN_NAME
FROM ALL_INDEXES A, ALL_IND_COLUMNS B
WHERE A.INDEX_NAME = B.INDEX_NAME
    AND A.TABLE_NAME = '테이블명'
ORDER BY COLUMN_POSITION;