Skip navigation.
Related Links
Drop all tables in a MySQL database

Drop all tables in a MySQL database

10
Oct
2006

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.

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!

17:03
12
Feb
2007

thanks for the solution

by Rik van der Helm (not verified)

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

Rik

13:10
26
Feb
2007

thank you!

by manuel (not verified)

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

05:31
26
Mar
2007

Alternate ...

by zoot (not verified)

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?

15:52
26
Mar
2007

Thinking about it - you might be right

by Nick

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!

00:45
23
Apr
2007

The alternate solution is

by Chekote (not verified)

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.

08:47
23
Apr
2007

Very true!

by Nick

This is a VERY good point.

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

19:22
1
May
2007

That solution doesn't work

by Anonymous (not verified)

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

11:09
2
May
2007

Shared hosting is limited anyway.

by Nick

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.

22:03
14
May
2007

Thanks

by Silveira Neto (not verified)

Thanks. Worked perfectly with me.

17:51
13
Jun
2007

Thank you

by xicobandito (not verified)

Thank you!

14:54
15
Jun
2007

reply

by premarin (not verified)

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?

14:01
31
Aug
2007

I'm using phpMyAdmin -

by genkml (not verified)

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.

16:39
5
Oct
2007

slight alternatve

by greggles (not verified)

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...

09:22
8
Oct
2007

Interesting alternative!

by Nick

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

21:10
8
Nov
2007

This is easier if you have root on the computer

by Chris (not verified)

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 :(

13:45
26
Nov
2007

Interesting

by Anonymoussys (not verified)

All tables, then apply "DROP" command?

13:47
26
Nov
2007

??

by Anonyff (not verified)

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

10:33
2
Jan
2008

There is a very good reason

by Onno (not verified)

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.

08:15
2
Apr
2008

downside

by Dorax (not verified)

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

00:30
4
Apr
2008

Nope

by Nick

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

04:48
9
Apr
2008

Thank you for great solution!

by Online (not verified)

Thank you for great solution!

19:29
11
Apr
2008

foreign keys

by dwight (not verified)

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...

20:28
11
Apr
2008

for windows

by dwight (not verified)

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

mysql -u --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.

12:47
13
Apr
2008

Nice explanation!

by Nick

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

05:44
14
Apr
2008

Your site is great and I

by Anonymous (not verified)

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

It'd be nice if you asked before taking stuff from my site. Contact me at webmaster [at] thingy - ma - jig . co . uk

This site was based on the Cobalt 2.0 Theme for phpBB written by Jakob Persson

Free MiniMac

Free MiniMac

Social Statistics
Search
Google



Weblinks

Add to Technorati Favorites

TGC Webring

CMS Drupal Showcase

Feedburner for ThingyMaJig

View Nicholas Thompson's profile on LinkedIn

My Twitter
  • Loading Twitter