MySQL - Drop all Tables in a Database Using a Single Command Line Command
Submitted by greggles on Mon, 2007-06-11 11:45.
in
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!
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.
Post new comment