Sunday, August 5, 2012

MySQL drop all tables that follow a pattern

Playing around with drupal I had to drop all tables that were created by a module. To do so I used the following chain of commands :
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 @@"

This will print out all the shell commands to drop the tables beginning with "Whatever_" (note the ^ regex notation). If you want it to actually execute those commands, remove the word "echo".
Let’s brake it down a bit:
  • Enumerate all tables in “your_database_name” connecting as “your_user” (using mysql)
  • Get the tables that start with “Whatever_” (using egreps or something similar). You can use any regular expression you want here.
  • Echo the drop statement to drop the tables that were matched (see the @@ notation) from the previous line (using xargs). If you remove echo then the system will execute the mysql command and will drop the tables.

No comments: