MySQL: Drop ALL tables of a database

After looking around for a while, here is a little recipe to efficiently drop all tables from a MySQL database.

Background story: If you have sufficient access rights to the MySQL server, you could easily drop database dbname; and create database dbname;

If circumstances don’t allow you to drop and create this way, here are two ways on how you could do it from the (Gnu/Linux) shell:

$ mysql -u<dbuser> -p<dbpass> -e “show tables” -N <dbname> | sed -e “s/.*/DROP TABLE IF EXISTS \`&\`;/” | mysql -u<dbuser> -p<dbpass> <dbname>


$ mysqldump -u<dbuser> -p<dbpass> –no-data –add-drop-table <dbname> | grep ^DROP | mysql -u<dbuser> -p<dbpass> <dbname>

Of course, you have to replace the following:

<dbuser>: Database user; e.g. “testuser” (without the quotes)
<dbpass>: Database password; e.g. “test1ng” (without the quotes)
<dbname>: Database name; e.g. “testdatabase” (without the quotes)


PS: [Compiled from post and comments under]

Leave a Reply

Your email address will not be published. Required fields are marked *