Gonzalo Rivero
Gonzalo Rivero
I am Gonzalo, I live in Salta, a city located in the NW of Argentina. I play the guitar and a little harmonica. I also like to bike.

How to drop all tables of MySQL database

A couple of days ago I was looking a way for easily drop all tables of a MySQL database.

Of course, there are more than one way to do it; here’s a couple of them:

Method 1: drop the database

This method occurred me only after I dropped all the tables of the database with the second one. Sometimes I choose the hard path just because. The idea is simple: run mysqladmin to drop the database, then create a new one with the same name, also with mysqladmin.

# mysqladmin -u root -p drop databasename
# mysqladmin -u root -p create databasename

As the mysqladmin help states:

create databasename Create a new database
drop databasename Delete a database and all its tables.

dropping and creating a database
erasing and creating a database

Method 2: use a sql script

Let’s suppose that for some obscure reason we can’t drop the whole database. I don’t remember my reason and right now I can’t made up a reason.

¿maybe to maintain some user privilege(s) on that database?. Anyway, as with other things we do, I did it this way because I can.

Generate the SQL commands

For this second method, I’ll use mysqldump to generate the script. The mysqldump utility performs produces a set of SQL statements that can be executed to reproduce the original database.

The trick here is to use an option, --add-drop-table, that adds a drop table command before every create table command. Run the following commands on your shell to generate the script:

echo "SET FOREIGN_KEY_CHECKS = 0;" > droptables.sql # we didn't care of foreign keys
mysqldump --add-drop-table -d -u root -p databasename | grep DROP\ TABLE >> droptables.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> droptables.sql #actually we do care of keys

The -d option on mysqldump is for not export the data (I’m about to drop all the tables anyway, I don’t need the rows of those tables). With the grep command I only print the DROP TABLE table name commands.

Run the script

Now all I have to do is to run that script to drop all tables:

mysql -p -u root databasename < droptables.sql
drop all tables via sql script
drop all tables via sql script

And this is it: I’ve got a shiny table-less database to start over.


Please enter your comment!
Please enter your name here

Latest articles

Join us on Facebook