Drop all tables in a MySQL database

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;

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.

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

Thanks. Worked perfectly with me.

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

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?

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.

Thank you for great solution!

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.

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.

Thank you. Saved alot of my time. -a

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

Thank you for great solution!

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

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.

Do NOT do the rm -rf method!

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

... found it very useful!

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

you lose all your users and all priviledges

Thanks Raja Shahed, that shure was simple and affective..

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

For some reason my shared hosting also didn't allow it.

Thanks anyway.

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

./truncate-db [user] [pass] [db]

Thanks a lot

Great idea. Helped 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! :-)