Simplified Complexity

Do you need to quickly drop all the tables in a MySQL database using the mysql client command line tools? Here's a quick command to do it...

Sure, you could just drop the database and recreate it effectively dropping all of the tables in a given database... but... what if you don't have the privileges to do that?

I have come across exactly this situation where I need to drop all the tables in a MySQL database but don't have the privileges to drop the database or to create new databases. If you do have drop/create table privileges you can generate a list of DROP TABLE statements using mysqldump, pipe it through grep (clean out the junk by only getting lines starting with DROP (the actual DROP statements)) and the piping that right back into mysql like so.

mysqldump --user=root --password=password --add-drop-table --no-data mydatabase | grep ^DROP | mysql --user=root --password=password mydatabase