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;

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.

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

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?

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.

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

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

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.