반응형

SELECT
      A.COLUMN_NAME     "컬럼명", 
      A.COLUMN_ID       "열순번",
      ( 
        -- 주키 번호를 읽어온다.(테이블명, 컬럼명 같은 필드의), INDEX가 여러개 선언시 UNIQUE한것으로 처리
        SELECT COLUMN_POSITION FROM   
        USER_IND_COLUMNS KK
        WHERE KK.TABLE_NAME=A.TABLE_NAME
          AND KK.COLUMN_NAME=A.COLUMN_NAME
          AND KK.INDEX_NAME=(SELECT MM.INDEX_NAME FROM USER_INDEXES MM WHERE MM.TABLE_NAME=KK.TABLE_NAME AND MM.UNIQUENESS = 'UNIQUE' AND LOGGING='YES'
                             AND MM.LEAF_BLOCKS = (SELECT MIN(LEAF_BLOCKS) FROM USER_INDEXES WHERE MM.TABLE_NAME=TABLE_NAME AND UNIQUENESS='UNIQUE' )
                            )
      ) "주키",
    A.NULLABLE        "NULL여부",
    DECODE(A.DATA_TYPE,'DATE',A.DATA_TYPE,A.DATA_TYPE || ' (' || DATA_LENGTH || ' Byte)') "데이터 형",
    A.DATA_DEFAULT "기본값" ,
    C.COMMENTS "한글필드명"
FROM COLS A,  USER_TAB_COMMENTS B, USER_COL_COMMENTS C
WHERE A.TABLE_NAME=B.TABLE_NAME
AND   A.TABLE_NAME=C.TABLE_NAME
AND   A.COLUMN_NAME=C.COLUMN_NAME
AND   A.TABLE_NAME = UPPER('테이블명')
ORDER BY A.COLUMN_ID
반응형

+ Recent posts