Drop MySQL databases matching some wildcard?

Question

The basic idea is to run “show tables” in your database, and use the results from that to select the
tables you want. I don’t think MySQL lets you do anything with the resultset from “show tables”,
but I’m probably wrong.

Here’s a quick-and-dirty solution using the shell:

mysql -u your_user -D your_database_name -e "show tables" -s | 
  egrep "^Whatever_" | 
  xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

That will print out all the shell commands to drop the tables beginning with “Whatever_”. If you want it to actually execute those commands, remove the word “echo”.

EDIT: I forgot to explain the above! I don’t know how familiar you are with shell scripting, but here goes:

mysql -u your_user -D your_database_name -e "show tables" -s

prints out a list of all your tables, with the header “Tables_in_your_database_name”. The output from that is piped (the | symbol means “piped”, as in passed-on) through the next command:

egrep "^Whatever_"

searches for any lines that begin (that ^ symbols means “beings with”) the word “Whatever_” and only prints those. Finally, we pipe that list of “Whatever_*” tables through the command:

xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

which takes each line in the list of table names, and inserts it instead of the “@@” in the command

echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

So if you had a bunch of tables named “Whatever_1”, “Whatever_2”, “Whatever_3”, the generated commands would be:

echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

Which would output the following:

mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

I hope that was enough detail, and that I’m not just beating anyone over the head with too much information. Good luck, and be careful when using the “DROP TABLE” command!

Leave a Comment Cancel reply