sqldb2sql-drop

How to drop all tables in db2


I want to drop all tables in db2 database with foregin keys, without drop and recreating.


Solution

  • If you also want to drop all views, indexes, foreign keys, etc.:

    select 'drop index "' || TRIM(INDSCHEMA) || '"."' || TRIM(INDNAME) || '";'
      from SYSCAT.INDEXES
      where UNIQUERULE = 'D'
      and INDSCHEMA = (select current schema from SYSIBM.SYSDUMMY1);
    
    select 'alter table "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || '" drop foreign key "' || TRIM(CONSTNAME) || '";'
      from SYSCAT.TABCONST
      where TYPE = 'F'
      and TABSCHEMA = (select current schema from SYSIBM.SYSDUMMY1)
    
    select 'alter table "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || '" drop unique "' || TRIM(INDNAME) || '";'
      from SYSCAT.INDEXES
      where UNIQUERULE = 'U'
      and INDSCHEMA = (select current schema from SYSIBM.SYSDUMMY1);
    
    select 'alter table "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || '" drop primary key;'
      from SYSCAT.INDEXES
      where UNIQUERULE = 'P'
      and INDSCHEMA = (select current schema from SYSIBM.SYSDUMMY1);
    
    select 'drop table "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || '";'
      from SYSCAT.TABLES
      where TYPE = 'T'
      and TABSCHEMA = (select current schema from SYSIBM.SYSDUMMY1);
    
    select 'drop view "' || TRIM(TABSCHEMA) || '"."' || TRIM(TABNAME) || '";'
      from SYSCAT.TABLES
      where TYPE = 'V'
      and TABSCHEMA = (select current schema from SYSIBM.SYSDUMMY1);