MySQL - Drop all Tables in a Database Using a Single Command Line Command
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
- connect to a specific mysql database and execute the command for showing tables
- find lines that match "Tables_in" and not show them
- find lines that match the + character and not show them
- use gawk to print out the words "drop table" followed by the table name (which is in $1) and then a semicolon
- pipe all of that back to the database you got the list from to drop those tables
Fun stuff and very handy!
Comments
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
awesome command line tip!
Thanks!
match table name to drop in phpmyadmin sql?
I found your command line information via web search and curious if you could tell me a way to drop table "table_*" from the phpmysqladadmin, as my ISP only allows web interface to the mysql backend.
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.
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".
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.
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.
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 [...]
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 [...]
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
:-)
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 ;)
Just what I was looking for
Yep, that is what I was looking to accomplish with my demo site. Thanks so much for this blog post!
Pingback
[...] via [...]
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
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.
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.
Post new comment