There are many situations in which you want to drop all tables in a MySQL database. You can easily drop the schema, but then all the permissions are lost too. There are several solutions out there, but they all require some manual effort. I wanted to get rid off it once and for all and constructed this easy script. With the following evil shell script you can easily drop all tables in your database.
Premisse: it uses a group on table_catalog which may not always be the correct grouping in your database. Please, feel free to adapt!
Just create a textfile like drop.sh and give it appropriate rights to execute. The paste the following content in the file and save it.
#!/bin/bash
echo Dropping database usr:$1, pwd:$2, db:$3
echo "set group_concat_max_len = 5000; select concat('set foreign_key_checks=0; ', group_concat(concat('drop table if exists ', table_name, ';') separator ' ')) from information_schema.tables where table_schema = '"$3"' group by table_catalog;" | mysql -u $1 -p$2 --disable-column-names $3 | mysql -u $1 -p$2 $3
Use ./drop.sh root root my_db et voilá…. all tables are gone.
Off course, things can be improved. I need to check the parameters and add some documentation…
Brain/Drain/Pain 