The best DROP-script for Mysql!

Posted: July 30, 2013 in MySQL, SQL, Ubuntu

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 and give it appropriate rights to execute. The paste the following content in the file and save it.

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 ./ 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…


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s