shsandeep wrote:
What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.
Cheers,
San.
I do this routinely for certain types of database maintenance. Here's
my UNIX shell script to do so. It generates a file that contains all of
the alter table statements to drop the FKs on the table. (One word of
caution, make sure you have a a copy of the FK defintions elsewhere in
the case you need to reapply them. The output of db2look can come in
handy here.) You'll have to replace some of the variables with info
pertaining to your database.
Regards,
Evan
################################################## ###
#!/bin/ksh
outfile=fkdrop.ddl
db2 connect to $yourdb user $youruserid using $yourpasswd
db2 "export to fkgen.dat of del \
select constname, tabschema, tabname, reftabschema, reftabname,
fk_colnames, pk_
colnames \
from syscat.references "
echo "-- Foreign Keys as of $(date)" $outfile
cat fkgen.dat |while read line
do
IFS=","
let i=0
for token in $line
do
arr[$i]=$token
let i=$i+1
done
print "\nALTER TABLE ${arr[1]}.${arr[2]}" >$outfile
print "DROP CONSTRAINT ${arr[0]} ;" >$outfile
done
