Monthly Archive: August 2017

Aug 18

List all base tables in a DB2 database

— — List DB2 base Tables — select sit.TABLE_NAME, sit.TABLE_TYPE, substr(sit.TABLE_NAME,2,instr(sit.TABLE_NAME,’_’)-2) as SYSTEM, sct.COLCOUNT, sct.CARD as ROWCOUNT from SYSIBM.TABLES sit inner join syscat.TABLES sct on sct.TABSCHEMA = sit.TABLE_SCHEMA and sct.TABNAME = sit.TABLE_NAME where sit.TABLE_SCHEMA=’@MVPADM’ and sit.TABLE_TYPE = ‘BASE TABLE’;

Aug 18

List unreferenced tables in a DB2 database

— — List DB2 base tables which don’t have References — SELECT sit.TABLE_NAME, ‘No References’ FROM SYSIBM.TABLES sit left outer join syscat.REFERENCES ref1 on ref1.tabschema = sit.TABLE_SCHEMA and ref1.tabname = sit.TABLE_NAME left outer join syscat.REFERENCES ref2 on ref2.reftabschema = sit.TABLE_SCHEMA and ref2.reftabname = sit.TABLE_NAME where ref1.constname is null and ref2.constname is null and sit.TABLE_SCHEMA = …

Continue reading »