[DB] 테이블명세서 및 인덱스정보 출력

#

-- 테이블정보
SELECT A.CLASS_NAME AS 테이블명
         , A.COMMENT AS [한글테이블명]
         , B.ATTR_NAME  AS 컬럼영문명
         , B.COMMENT     AS 한글컬럼명
         , B.IS_NULLABLE     AS [NULL여부]
         , B.DATA_TYPE     AS 데이터타입
         , B.PREC  AS  데이터길이
         , CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보
         , CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS  FK정보
         , B.DEFAULT_VALUE AS 제약조건
         -- ,  B.DEF_ORDER + 1 AS 컬럼순서
  FROM DB_CLASS A
            INNER JOIN DB_ATTRIBUTE B
                ON B.CLASS_NAME = A.CLASS_NAME
            LEFT JOIN DB_INDEX C
                 ON C.IS_PRIMARY_KEY = 'YES'
                AND C.CLASS_NAME = B.CLASS_NAME
            LEFT JOIN DB_INDEX_KEY D
                 ON  D.CLASS_NAME = C.CLASS_NAME
                AND D.INDEX_NAME = C.INDEX_NAME
                AND D.KEY_ATTR_NAME = B.ATTR_NAME
            LEFT JOIN DB_INDEX E
                 ON E.IS_FOREIGN_KEY = 'YES'
                AND E.CLASS_NAME = B.CLASS_NAME
            LEFT JOIN DB_INDEX_KEY F
                 ON  F.CLASS_NAME = E.CLASS_NAME
                AND F.INDEX_NAME = E.INDEX_NAME
                AND F.KEY_ATTR_NAME = B.ATTR_NAME
 WHERE A.OWNER_NAME = 'TESTSCHEMA' AND (A.CLASS_NAME LIKE 'p_%' OR A.CLASS_NAME LIKE 't_%')
 ORDER BY A.CLASS_NAME, B.DEF_ORDER
;
 
-- 인덱스정보
SELECT DISTINCT a.class_name AS [테이블명],
    a.index_name AS [인덱스명],
    b.key_attr_name AS [인덱스컬럼명],
    a.is_unique AS [UNIQE여부],
    a.is_primary_key AS [PK여부],
    a.is_foreign_key AS [FK여부],
    b.key_order AS [키순서]
FROM db_index a, db_index_key b
WHERE a.index_name = b.index_name
  AND a.class_name = b.class_name
    AND (a.class_name LIKE 's_%' OR a.class_name LIKE 's_%')
ORDER BY a.index_name, b.key_order
;
 
 

#