4

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Roman Iuvshin
  • 1,872
  • 10
  • 24
  • 40
  • well, presumably the same way as you upvoted the previous 3 times, plus accept some more answers (if they helped you). – Mitch Wheat Nov 24 '11 at 08:26

3 Answers3

8

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);
user3151902
  • 3,154
  • 1
  • 19
  • 32
  • 1
    At first thought it is not working BUT The selects written above generates code which then needs to be executed (and then works) :-) – Lonzak May 12 '23 at 16:02
3

If you are in Linux or Unix enviroment.

#!/bin/ksh

## load profile of your instance owner

db2 "connect to <db_name">
db2 -x "select tabschema,tabname from syscat.tables where type='T' and tabschema not like 'SYS%' with ur"|while read a b

do
   db2 "load from /dev/null of del replace into $a.$b nonrecoverable"
done

return 0

This script delete the data into the tables.

Carlos Guzmán
  • 151
  • 1
2
db2 "Select 'DROP TABLE ', tabname, ';' from syscat.tables where owner='DBUSER'" >> filename

Delete the very first line and last line of the file and run it using,

db2 -tvf filename.

This way we can also save the log of tables we have dropped.

PS: Make sure once, only the tables you wanted are present in the file. Dont by mistake, drop some system tables.

Toto
  • 89,455
  • 62
  • 89
  • 125
Bharath
  • 543
  • 1
  • 6
  • 11