[10G] 컬럼 정보 보기 뷰

/********************************************************************************************
-- Title : [10G] 컬럼 정보 보기 뷰
-- Reference : 웹검색 - 다수
-- Key word : column 컬럼
********************************************************************************************/

SELECT TC.TABLE_NAME AS TABLE_NAME
     , TC.COLUMN_NAME AS COLUMN_NAME
     , TC.DATA_TYPE AS DATA_TYPE
     , TC.DATA_LENGTH AS COLUMNLENGTH
     , TC.CHAR_COL_DECL_LENGTH AS CHARACTER_MAXIMUM_LENGTH
     , TC.COLUMN_ID AS ORDINAL_POSITION
     , TC.CHAR_LENGTH AS CHARACTER_MAXIMUM_LENGTH
     , TC.DATA_PRECISION AS NUMERIC_PRECISION
     , TC.DATA_SCALE AS NUMERIC_SCALE
     , TC.NULLABLE AS IS_NULLABLE
     , TC.DATA_DEFAULT AS COLUMN_DEFAULT
     , UPC.UPDATABLE AS UPDATABLE
     , UPC.INSERTABLE AS INSERTABLE
     , UPC.DELETABLE AS DELETABLE
     , UCC.ISPRIMARYKEY
     , UCC.ISFOREIGNKEY
     , UIC.HASUNIQUECONSTRAINT
FROM USER_TAB_COLUMNS TC
   , USER_UPDATABLE_COLUMNS UPC
   , ( SELECT UC.TABLE_NAME
            , UCC.COLUMN_NAME
            , MIN (DECODE (UC.CONSTRAINT_TYPE, 'P', 'Y')) ISPRIMARYKEY
            , MIN (DECODE (UC.CONSTRAINT_TYPE, 'R', 'Y')) ISFOREIGNKEY
       FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
       WHERE UC.TABLE_NAME = 'TTT2' 
       AND   UCC.TABLE_NAME = UC.TABLE_NAME
       AND   UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
       AND   UC.STATUS = 'ENABLED'
       AND   UC.CONSTRAINT_TYPE IN ('P', 'R')
       GROUP BY UC.TABLE_NAME, UCC.COLUMN_NAME
     ) UCC
   , ( SELECT UI.TABLE_NAME
            , UIC.COLUMN_NAME
            , DECODE (UI.UNIQUENESS, 'UNIQUE', 'Y') HASUNIQUECONSTRAINT
       FROM USER_INDEXES UI
          , USER_IND_COLUMNS UIC
       WHERE UI.TABLE_NAME = 'TTT2'
       AND   UIC.TABLE_NAME = UI.TABLE_NAME
       AND   UIC.INDEX_NAME = UI.INDEX_NAME
       AND   UI.UNIQUENESS = 'UNIQUE'
     ) UIC
WHERE TC.TABLE_NAME = 'TTT2'
AND   TC.TABLE_NAME = UCC.TABLE_NAME (+)
AND   TC.TABLE_NAME = UIC.TABLE_NAME (+)
AND   TC.TABLE_NAME = UPC.TABLE_NAME
AND   TC.COLUMN_NAME = UPC.COLUMN_NAME (+)
AND   TC.COLUMN_NAME = UCC.COLUMN_NAME (+)
AND   TC.COLUMN_NAME = UIC.COLUMN_NAME (+)
ORDER BY TC.COLUMN_ID;