The Penguinlinux.com Blog

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!