DB2 Script - Table Column Info

DB2 테이블 및 컬럼 목록 정보 (통계정보) 포함

-- COLUMN INFO
-- 실제 PK CONSTRAINT를 생성하지 않고 PK 이름으로 인덱스만 생성한 경우를 찾기 위한 로직이 포함되어 있음.
SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME, C.REMARKS, C.COLNO, C.TYPENAME, C."LENGTH", C."SCALE" , C."NULLS", C."DEFAULT",
    C.KEYSEQ, U.PK_IND_COL_SEQ,
    P.DATAPARTITIONKEYSEQ,
    C.COLCARD, C.HIGH2KEY, C.LOW2KEY, C.AVGCOLLEN, C.NUMNULLS
FROM SYSCAT.COLUMNS C 
LEFT OUTER JOIN SYSCAT.DATAPARTITIONEXPRESSION P ON C.TABSCHEMA = P.TABSCHEMA AND C.TABNAME = P.TABNAME AND C.COLNAME = P.DATAPARTITIONEXPRESSION
LEFT OUTER JOIN (SELECT I.INDSCHEMA, I.INDNAME, I.TABNAME, I.COLNAMES, IC.COLNAME, IC.COLSEQ AS PK_IND_COL_SEQ
            FROM SYSCAT.INDEXES I 
            INNER JOIN SYSCAT.INDEXCOLUSE IC ON I.INDSCHEMA= IC.INDSCHEMA AND I.INDNAME = IC.INDNAME
            WHERE 1=1
            AND RIGHT(I.INDNAME,3) = '_PK') U ON C.TABSCHEMA = U.TABSCHEMA AND C.TABNAME AND U.TABNAME AND C.COLNAME = U.COLNAME 
WHERE 1=1
AND C.TABSCHEMA NOT LIKE 'SYS%'
ORDER BY C.TABSCHEMA, C.TABNAME, C.COLNO 
WITH UR;