Drop all tables in a MySQL database

This morning I am faced with a task that will involve repeatedly dropping and reimporting a lot of data. MySQL has DROP TABLE and DROP DATABASE but there is no command to drop all tables or truncate the database.

[adsense:468x60:4496506397]

After finding a reference on the MySQL Lists (http://lists.mysql.com/mysql/193430) I started playing with the idea. I didn't want to have to dump into one file and then run that query. Also - the problem with the above concept is that it drops the table and then recreates it - not what i wanted!

I then looked into using the pipe and grep features in Linux. Now I was getting somewhere! A few tweaks later and this is what I got:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

In the above, [USERNAME], [PASSWORD] & [DATABASE] are all the details for your database. You might not need the username and password fields - depends on your setup!

Comment Icon

67 Comments

The most recent comment was on Thu, 24th Nov 2011 - 10:29

He great, this solution for 'drop all table'. Was looking for it quite a while already.

Rik

This is a great solution for the "drop tables from database" problem, thank you! Looked for it for a long time...

How about logging into mysql and then using DROP and CREATE database.

# mysql -uroot -p 
mysql> DROP DATABASE dbname;
mysql> CREATE DATABASE dbname;

I just did that and it seemed to effectively empty the database. Is there a downside to doing it this way?

I have a feeling that does do the job just as well - although it doesn't preserve any settings applied to the database (for example, character set or collation).

Still - its a very good point!

The alternate solution is only going to work if you have permissions to create a new database. If you only have one database that you can work with, and no permissions outside that db, that isn't going to help.

This is a VERY good point.

Thanks for bringing this up Chekote as I'd not considered it before.

That solution doesn't work for those of us on shared hosting services who don't have permissions to create and drop databases.

If you're on shared hosting there is often very little that you can do in terms of usefull and cool things. They're usually VERY restrictive.

Thank you!

Is there a way in phpMyAdmin to drop all tables from a MySQL database without deleting the database, just removing all contents? Sort of like a wildcard drop?

I'm using phpMyAdmin - 2.10.0.2 with my web hosting. There's an option to Select All tables, then apply "DROP" command. It deleted all tables in the database, just as you wanted.

Alternatively, you can also tick as many tables as you want before selecting the drop command. Very handy if you want to delete some tables but not all.

Here's a slight alternative to the solution:

 mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \
gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

From my mysql drop all tables using single command.

I'm not sure if there's any good reason to use one or the other. Obviously some command line options (like username) are missing from mine...

Thanks for that Greggles - I'll have to look into the 'gawk' command - not sure I've ever really seen it used before!

I did
# rm -f /var/mysql//*
which works fine if it's your own computer.

I needed to delete all the tables because my database became corrupted when two different versions of a client program on my local net were writing to the same database :(

All tables, then apply "DROP" command?

Anonyff's picture

I'm not sure if there's any good reason to use one or the other

There is a very good reason to use 'show tables' and not mysqldump. If you have a large database 'show tables' will offer a superior performance.

The only downside is that you might have the GRANT new permissions on the database again.

From my experience, dropping and creating a table doesn't require recreation of the permissions.

BTW, if you have foreign key constraints, i think you have to do:
Set foreign_key_checks=off;
before doing the drops (at least i had to). (using MySql 5.0.)

Too bad I don't have grep or gawk on windows...

ok, i found a note on mysql forums, & with a little adaptation came up with this for a Windows environment:

mysql -u<name> --execute="SELECT concat('DROP TABLE ',table_name,';') INTO OUTFILE 'c:/drop.sql' FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydbname';source c:/drop.sql;" mydbname

This requires read access to information_schema.TABLES -- i don't know if that's typically available (to non-admins) or not! One other thing -- the output file (drop.sql above) can NOT exist -- so you need to delete it each time. (Of course, if the tables are always the same, you can just do "source c:/drop.sql;" each time after the first!)

...and insert "set foreign_key_checks=off;" before the "source" command if you have FK constraints.

Thanks for posting that - much appreciated. Definitely an alternative way of doing this.

Your site is great and I really appreciate it!
I have always enjoyed reading your site.

Oh yes, there are versions of grep and gawk for windows.
Here's how you do it:

Download the latest UnixUtils ZIP from http://sourceforge.net/projects/unxutils
These are precompiled versions of basic Unix tools for Windows, including grep & gawk.

Unpack into some local folder (e.g. into C:\Program Files\UnxUtils\)

Then use this code in a batch file (adapt the first 5 lines as needed):

set UnixUtilsPath=C:\Program Files\UnxUtils\usr\local\wbin\
set MySQLPath=C:\Program Files\MySQL\MySQL Server 5.0\bin\
set dbname=mydatabase
set usr=myusername
set pwd=mypassword
"%MySQLPath%mysql" -u%usr% -p%pwd% %dbname% -e "show tables" | "%UnixUtilsPath%grep" -v Tables_in | "%UnixUtilsPath%gawk" "{print \"drop table \" $1 \";\"}" | "%MySQLPath%mysql" -u%usr% -p%pwd% %dbname%

Enjoy!
Jpsy

For those of you who are using forgen keys in your DB,
You should probably add the line
"SET FOREIGN_KEY_CHECKS = 0;"
to the head of that pipe, otherwise the deletion in alphabetical orer won't work.

sweet!

This just saved me a lot of time.

An even shorter version of greggles excellent alternative reference:

 mysql -BNe "show tables" | awk '{print "drop table " $1 ";"}' | mysql

-B --batch - Tab delimited output, one line per record / no history
-N --skip-column-names - Do not write column names in results

(In the example above I am using .my.cnf to handle user/pass/database)

there should be a permission problem when you create database

Anonymous's picture

Thank you for this - worked perfectly in phpMyAdmin w/out losing the database itself on a shared server.

I'm not sure if you care, but as for an update on the subject, MySQL has a "Truncate" command that will do exactly what you want. It will drop all of the data, keeping the structure and preferences of the tables in place.

And here is the command for getting the truncate commands for a given db.

mysqldump -u[USERNAME] -p[PASSWORD] --no-data --compact --add-drop-table | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g'

Note that i added "--compact" so mysqldump prints less data making the script slightly faster to execute. ;)
Thanks for the hint!

You can get errors if you don't use backticks around your table names. This is what I used (assuming user/pass is in ~/.my.cnf). I've included the foreign_key_checks switch.

mysql --silent --skip-column-names dbname -e "show tables" | /usr/bin/gawk '{print "set foreign_key_checks=Off; drop table `" $1 "`;"}' | mysql dbname

Hai
while searching i found the drop database method that explained,my problem while creating a database (asd)no problem,and the same name a new database also created it named as (asd(2)).the problem is i cannot drop, delete or do any thing even select

mysql> DROP DATABASE asd(2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '(2)'
at line 1
mysql>

pls help me

You cant have brackets in a table or database name...

Dropping a database and creating it doesn't work if you have limited access to the database, i.e. it's locked to a particular user. It seems that the access for that user is also deleted so you need to recreate that user, and set up the priviledges again.

# rm -f /var/mysql//* works if you have both read/write access to the files...

But let's say that you done that and want to restore a backup taken with mysqlhotcopy (by coping the backup file back) and your mysql users rights are limited to that particular database you might end up still seeing the deleted data in your queries due to server caching and not being able to flush the cache since you don't have the rights to do that.

By dropping the table first you'll get around this.

for table in `mysql -u root -pPASSWORD database -e "show tables;" | cut -d\| -f2` ; do echo "truncate table $table;"; done | grep -v Tables_in_ | mysql -u root -pPASSWORD database

A little longer, but also a little more efficient because it does the drop (of all tables) in one shot:

mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD  YOUR_DBSCHEMA_NAME

One of the easiest way to drop all tables or multiple tables at a time - grab a phpMyAdmin. Select all tables. Choose the dropdownlist WITH SELECTED - DROP. You are done. It works like charm. Regards. Raja Shahed

The version posted a few comments up is the fastest and most compatible option. Here it is again:

mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD -BNe "show tables" YOUR_DBSCHEMA_NAME | tr '\n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=YOUR_USERNAME --password=YOUR_PASSWORD  YOUR_DBSCHEMA_NAME
mysql -u uname -p password dbname -e "show tables" | \
grep -v Tables_in|awk '{print "drop table",$1";"}' | \
mysql -u uname -p password dbname
Grepping out "+" isn't necessary when the mysql output is piped, it already removes the borders around the data and awk works fine in place of gawk too in this instance.

you lose all your users and all priviledges

You shouldn't lose any privileges simply by dropping tables.

Can you provide an example for this?

phpMyAdmin does work.

But for me it seems massive overkill to have to install a whole package to do a simple task.

Hello,

Thank you guys for the great information!! I am had few issues on my website www.hairinfo.net and I used all the infos provided here and I fixed most of it. I have added this page to my fav lol.

thanks again :).

<?php
mysql_connect('localhost', 'user', 'password');
mysql_select_db('database');
$qry =mysql_query("SHOW TABLES;");
while($res = mysql_fetch_array($qry))
{
$dropqry = mysql_query("DROP TABLE $res[0];");
echo $res[0]." deleted <br />";
}
echo "<br /> all tables deleted <br />";
?>

The downside to the "Drop/Create database" method is that it required a permission to do that - not all hosts give you permission to drop or create whole databases, only tables.

I think generating the drop statements in a files, gives you more control since you can review the statements before you fire it on the database, especially in a production environment.

Running the drop statements from a single command line can messup the database if things are wrong in the command.

Yes your method is entirely deleting the database, so as the other gentlemen pointed out you will lose your settings, not to mention your permissions (users!).

I found this works to truncate all the tables in a database and also overcomes issues with foreign keys and foreign key checks.

function truncateAllTables ($database, $username, $password) {
  	exec("echo 'SET foreign_key_checks = 0;\n' && mysqldump --user=$username --password=$password --add-drop-table --no-data $database && echo 'SET foreign_key_checks = 1;\n' | grep ^DROP | mysql --user=$username --password=$password $database");  
  }

I found this works to truncate all the tables in a database and also overcomes issues with foreign keys and foreign key checks.

public static function truncateAllTables ($database, $username, $password) {
  	exec("(echo 'SET foreign_key_checks = 0; ' && mysqldump --user=$username --password=$password --add-drop-table --no-data $database && echo 'SET foreign_key_checks = 1; ') | grep ^DROP | mysql --user=$username --password=$password $database");  
  }

"not to mention your permissions"

I have just tested deleting a whole db and recreating it, and permission were kept intact!

Great tip - thanks. :)

Just a minor improvement for folk who use foreign keys and still prefer a one liner:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

kudos... I have turned it to shell script

#!/bin/bash
mysqldump -u$1 -p$2 --add-drop-table --no-data $3 | grep ^DROP | mysql -u$1 -p$2 $3

to call it as
./truncate-db [user] [pass] [db]

Thanks a lot

There is no downside as far as I know... However in some cases you don't have enough permissiones to drop or create a database.

Like those easy tricks i couldnt figure out.
Worked nice for me, thanks! :-)

Add new comment

Filtered HTML

  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <pre> <ul> <ol> <li> <dl> <dt> <dd> <img> <p>
  • You can use BBCode tags in the text. URLs will automatically be converted to links.
  • You can enable syntax highlighting of source code with the following tags: <code>, <pre>, <bash>, <css>, <html>, <js>, <jquery>, <mysql>, <php>. PHP source code can also be enclosed in <?php ... ?> or <% ... %>.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.