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.
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
And this is it: I’ve got a shiny table-less database to start over.
