[오라클] 테이블 엔티티 명세서 쿼리 > db

--엔티티 명세서 쿼리 예

SELECT A.TABLE_NAME
     , A.COLUMN_ID
     , B.COMMENTS
     , A.COLUMN_NAME
     , A.DATA_TYPE
     , DECODE(A.DATA_TYPE, 'NUMBER', A.DATA_PRECISION || DECODE(A.DATA_SCALE, 0, '', ',' || A.DATA_SCALE)
                         , 'DATE', '', A.DATA_LENGTH) AS WIDTH
     , ''
     , DECODE(D.CONSTRAINT_TYPE , 'P', 'Y', '') AS PK_KEY_YN
  FROM COLS A
     , USER_COL_COMMENTS B
     , USER_IND_COLUMNS C
     , USER_CONSTRAINTS D
 WHERE A.TABLE_NAME IN ('RM02L800','RM02L810','RM02L820','RM02L821','RM02L822'
                       ,'RM02L830','RM02L840','RM02L850','RM02L870','RM02N890'
                       ,'RM02N899')
   AND 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 C.TABLE_NAME  = D.TABLE_NAME(+)
   AND C.INDEX_NAME  = D.INDEX_NAME(+)
 ORDER BY A.TABLE_NAME
        , A.COLUMN_ID

-- 인덱스 명세서 쿼리 예

SELECT C.TABLE_NAME
     , C.INDEX_NAME
     , DECODE(D.CONSTRAINT_TYPE, 'C', 'CHECK CONSTRAINT ON A TABLE'
                               , 'P', 'PRIMARY KEY'
                               , 'U', 'UNIQUE KEY'
                               , 'R', 'REFERENTIAL INTEGRITY'
                               , 'V', 'WITH CHECK OPTION, ON A VIEW'
                               , 'O', 'WITH READ ONLY, ON A VIEW', D.CONSTRAINT_TYPE)  AS CONSTRAINT_TYPE
     , C.DESCEND
     , C.COLUMN_NAME
  FROM USER_IND_COLUMNS C
     , USER_CONSTRAINTS D
 WHERE C.TABLE_NAME IN ('RM02L800','RM02L810','RM02L820','RM02L821','RM02L822'
                       ,'RM02L830','RM02L840','RM02L850','RM02L870','RM02N890'
                       ,'RM02N899')
   AND C.TABLE_NAME  = D.TABLE_NAME(+)
   AND C.INDEX_NAME  = D.INDEX_NAME(+)
ORDER BY C.TABLE_NAME
       , D.CONSTRAINT_TYPE DESC
       , C.INDEX_NAME
       , C.COLUMN_POSITION