DB2 Script - Table Info

DB2 테이블 목록 및 정보 출력

SELECT T.TABSCHEMA, T.TABNAME, T.REMARKS,
    CASE WHEN T.TYPE = 'A' THEN 'ALIAS' 
        WHEN T.TYPE = 'T' THEN 'TABLE'
        WHEN T.TYPE = 'G' THEN 'Temporary Table'
        WHEN T.TYPE = 'V' THEN 'VIEW' END AS TABLE_TYPE,
    T.CREATE_TIME, T.STATS_TIME, T.COLCOUNT, T.CARD, T.TBSPACE, T.INDEX_TBSPACE, T.LONG_TBSPACE,    
    T.AVGROWSIZE, PE.DATAPARTITIONEXPRESSION, P.PART_MIN_LOWVALUE, P.PART_MAX_LOWVALUE, PART_MAX_CARD
-- ROUND((DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE)/2034,2) AS TABLE_SIZE_MB,
-- ROUND((INDEX_OBJECT_P_SIZE)/204,2) AS INDEX_SIZE_MB
FROM SYSCAT.TABLES T
LEFT OUTER JOIN SYSCAT.DATAPARTITIONEXPRESSION PE ON T.TABSCHEMA = PE.TABSCHEMA AND T.TABNAME = PE.TABNAME
LEFT OUTER JOIN (
        SELECT TABSCHEMA, TABNAME, MIN(LOWVALUE) AS PART_MIN_LOWVALUE, MAX(LOWVALUE) AS PART_MAX_LOWVALUE, MAX(CARD) AS PART_MAX_CARD
        FROM SYSCAT.DATAPARTITIONS
        WHERE 1=1
        GROUP BY TABSCHEMA, TABNAME
        ) P ON T.TABSCHEMA = P.TABSCHEMA AND T.TABNAME = P.TABNAME
-- LEFT OUTER JOIN SYSIBMADM.ADMINTABINFO TI ON T.TABSCHEMA = TI.TABSCHEMA AND T.TABNAME = TI.TABNAME
WHERE T.TABSCHEMA NOT LIKE 'SYS%'
WITH UR;

* TABLE SIZE 정보를 출력하면 속도가 느릴 수 있음. 필요에 따라 주석 풀고 사용하시길