Drop all tables in a MySQL database

This morning I am faced with a task that will involve repeatedly dropping and reimporting a lot of data. MySQL has DROP TABLE and DROP DATABASE but there is no command to drop all tables or truncate the database.


After finding a reference on the MySQL Lists (http://lists.mysql.com/mysql/193430) I started playing with the idea. I didn't want to have to dump into one file and then run that query. Also - the problem with the above concept is that it drops the table and then recreates it - not what i wanted!

I then looked into using the pipe and grep features in Linux. Now I was getting somewhere! A few tweaks later and this is what I got:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

In the above, [USERNAME], [PASSWORD] & [DATABASE] are all the details for your database. You might not need the username and password fields - depends on your setup!