MySQL - Drop all Tables in a Database Using a Single Command Line Command

Submitted by greggles on

Let's say you need to drop all tables in a mysql database. How do you do that?

You could use a gui, but that's not fun.

You're a shell jockey so you want a commandline:

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

(that's all one line, but if I do it as a line then it screws up my theme - go figure).

This assumes that you are running in passwordless mode. See "man mysql" for tips on how to pass in passwords in another manner.

What this does is

  1. connect to a specific mysql database and execute the command for showing tables
  2. find lines that match "Tables_in" and not show them
  3. find lines that match the + character and not show them
  4. use gawk to print out the words "drop table" followed by the table name (which is in $1) and then a semicolon
  5. pipe all of that back to the database you got the list from to drop those tables

Fun stuff and very handy!

Category: 
Technology
People Involved: 
Greg
timeline: 
past

Comments

Jpsy replied on Permalink

for Windows (using grep and gawk from UnxUtils)

Here's how you can do it on Windows:

Download the latest UnxUtils 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

siliconmeadow replied on Permalink

Dropping tables within phpMyAdmin

As Greg said, the previous comment drops the whole database. If you can't use Greg's command line solution and have to use phpMyAdmin to drop all tables:

  1. Click on the "Structure" tab in the main window;
  2. Scroll to the bottom of the page and click the "Check All" link;
  3. In the "With selected" select item, choose "Drop";
  4. Click yes on the next question.

tcar replied on Permalink

Go to phpMyAdmin

Go to phpMyAdmin homepage

Click 'Databases' to get a list of your databases

Check the checkbox next to the database you want to drop

Click the icon with the X (it's a red x) below the list of databases (mouse rollover brings up 'Drop')

Click 'Yes' when you get warning 'You are about to DESTROY a complete database!'.

Voila! database dropped.

greggles's picture greggles replied on Permalink

not exactly

Well, that requires phpmyadmin. The point of my script is not to use a gui.

Also, you just dropped the whole database. The point of my script is to delete the tables even if you don't have permission to drop/create a database. That's handy, for example, with a web application where you want a "clean slate".

Rex M replied on Permalink

TCAR ?

TCAR,
the former admin placed multiple instances in one large mysql db, each instance has a "table_prefix_"

Don't won't to drop the entire db, just the tables from one instance.

greggles's picture greggles replied on Permalink

try breaking apart the script I gave you

In the middle are a bunch of "grep" commands which limit the set of tables to work with. Just add in another "grep table_prefix |" to the middle of the command in the original post and that should work.

weatheredwatcher replied on Permalink

Pingback

[...] My mySql GUI tools were not upto the challenge. I found a nifty little bit of code on the internet (Check out the original here) that drops all the tables in database. Drops ‘em like they were [...]

nsslive &ra... replied on Permalink

Pingback

[...] line to drop all the tables was from this page here (thanks!). My install.sql can also include the SQL inserts for default values (I will post the code [...]

Matteo replied on Permalink

Tip: instead of using: |

Tip: instead of using:

| grep -v Tables_in | grep -v "+"

you can comfortably use the nice mysql command options --silent and --skip-column-names; so the command line would became:

mysql --silent --skip-column-names -u uname dbname -e "show tables" | \
gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

:-)

seagen replied on Permalink

hi dude.....the two option

hi dude.....the two option about --silent and --skip-colum-names ,both are so nice.....cool...

thanks for your tip ;)

Jamie replied on Permalink

Just what I was looking for

That's handy, for example, with a web application where you want a "clean slate".

Yep, that is what I was looking to accomplish with my demo site. Thanks so much for this blog post!

Lon F. Binder replied on Permalink

Better linux approach

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

Raja Shahed replied on Permalink

Drop all tables from a MySQL database in one shot

Deleting every thing is easier than Creating. Access to your Mysql tables thru PHPMYADMIN. Click the database at the left hand side to display all tables at the right hand side frame. Now select tables to delete by clicking their regarding checkbox. At the bottom or under the tables listing there is a Drop down list : With Selected - Choose DROP. pops a confirmation window. Click OK.Thats all. Some web hosters do not allow you creating or deleting a DB - in such case it comes handy. In this way you can delete - some or all tables from a database.

greggles's picture greggles replied on Permalink

phpmyadmin: do not want!

Why would I bother installing a webgui to the database when there is a command line?

Especially when that webgui to the database is one which has had security updates - so now I have to update my core software and phpmyadmin...no thanks.

greggles's picture greggles replied on Permalink

foreign keys

You have relationships on keys between tables. The script doesn't take those into account and is trying to drop a table that is required by another table.

You will probably have to delete them in a more specific order OR do some sort of option to force the delete like from this stack overflow article.

Or, if you run my command from above two or three times it should eventually work because it eventually drops the tables in the right order.

Tobias Sjösten replied on Permalink

A variant

You could also try a shorter (number of characters) variant which uses the fact that mysqldump by default adds a DROP instruction:
$ mysqldump -uroot -p dbase|grep ^DROP|mysql -uroot -p dbase

Tobias Sjösten replied on Permalink

Drush command

If you're using Drupal and Drush, and I suspect you are, then you could use my sql-drop command:

http://github.com/tobiassjosten/drush-sql-drop

greggles's picture greggles replied on Permalink

Thanks, Tobias. I posted this

Thanks, Tobias. I posted this back on 2007 when drush was newborn and did very little. I also wanted something that would work in limited environments where I might not be able to install Drush and something that would work regardless of whether the database was being used for Drupal.

Any chance you're going to incorporate that into the main drush?

Tobias Sjösten replied on Permalink

I would love to! It was

I would love to! It was basically done as a proof of concept and could probably be improved a lot. But I'll go ahead and bring that up in the Drush issue queue.

Alejandro Senges replied on Permalink

Drop Multiple Tables

Your first script worked fine!!! I just added some minimal changes to fit my specific requirements and put it into a bash file like this

!bin/bash

mysql -u dbuser --password=dbpasswd dbname -e "show tables like 'prefix%'" | grep -v Tables_in | grep -v "+" | awk '{print "SET FOREIGN_KEY_CHECKS = 0; drop table " $1 ";"}' | mysql -u dbuser --password=dbpasswd dbname

Kevin Kaland replied on Permalink

Worked like a charm

I just needed this to clear a database, and for some reason the oracle guided me here. Coincidence? Probably not, but anyway I guess the bubble did me well here, and yeah...it worked. I made a similar edit to Alejandro to add the password.

So, thanks!

@wizonesolutions