DROP all MySQL tables from the command line

I had a situation today where I wanted to drop all the tables in a database, but didn't have access to a UI like phpMyAdmin. The easiest thing to do would have been to drop the entire database then re-create it, but I didn't have permissions to drop an entire database.

After searching around online, this is the best solution I found:

mysqldump -u username -p --no-data dbname | grep ^DROP > drop.sql
mysql -u username -p dbname < drop.sql
rm drop.txt

Simple, and only requires 2 lines (ok, a 3rd line for cleanup).

What these commands do is basically generated a mysqldump file (which included DROP commands by default), then filter the output to only show the lines that have the DROP statements. The arrow (>) redirects the output to a file. The second line then imports those SQL statements into the database.

The original suggestion I found looked like this:

mysqldump -u username --no-data dbname | grep ^DROP | mysql -u username

The only problem I ran into was I needed to specify a password. When I added the -p password argument, the first mysqldump command got passed an empty password. I could have included the password in the command itself, but that's a security no no.



An other way

You can also generate an sql script from the information_schema database:

Select concat('DROP TABLE database_name.', table_name,';') from information_schema.TABLES where table_schema='database_name';

Which gives:

DROP TABLE database_name.table_name1;
DROP TABLE database_name.table-nameN;

You can pipe it to be executed directly (I like to verify first ;-) ) I prefer this syntax than the DROP TABLE IF EXISTS table_name1;