Dropping multiple tables from mysql command line

Like most all SQL servers, mysql supports the "LIKE" operator, which allows you to do simple wildcard matches using the percent sign (%) as the wildcard.SHOW TABLES LIKE "%user%";shows you all your tables (in the working database) which have "user" in their name.

Unfortunately this operator doesn't work with the DROP syntax. You can't sayDROP TABLE LIKE "%users%";and drop all the tables with "user" in their name. You have to list them each individually.

In my case I want to remove just one drupal website which is part of a multi-site installation. So the tables for each different site have their own prefix before the standard drupal table name to tell which table goes with which website. But a drupal website is around 70 tables or so; more if you've installed any extra modules. So I do not want to list them by hand; nor do I have access to a tool like phpmyadmin where I can simply check them all off and click the DROP button. I really want to runDROP TABLE LIKE "mysite_%"; and drop all the tables that begin with the prefix "mysite_" in one blow.

Fortunately we can do this in the mysql command line, but as a two step process. Step 1 creates the DROP TABLE statement with all the table names listed so we don' t have to do it ourselves. Then in step 2 we simply copy and paste that statement back into the mysql command line to run it.

Here's how to create the statement. SELECT CONCAT("DROP TABLE ", GROUP_CONCAT(table_name), ";") FROM information_schema.tables
WHERE table_schema = "drupal6" AND table_name LIKE "mysite_%";

This says to go through mysql's "information_schema.tables" table (which has information about every table in every database) and concatenate all the table names from the "drupal6" database which begin with "mysite_". Then it takes that string and concatenates it with the string "DROP TABLE " at front and a semi-colon at the back to create a mysql statement to drop ALL the tables beginning with "mysite_". (Fortunately the GROUP_CONCAT command creates a comma-separated list by default, which is what we want.)

The results look like this: DROP TABLE mysite_access,mysite_actions,mysite_actions_aid,mysite_authmap,
<snip>
mysite_vocabulary,mysite_vocabulary_node_types,mysite_watchdog;

I'm leaving out most of the table names, but you get the idea. Now I've got a DROP TABLE statement with all the tables listed that I can copy and paste right back into the mysql command line and hit ENTER. Much quicker than copying and pasting each table into the statement by hand.

One thing to notice, however; if the length of the table names returned is longer than 1024 characters, the result will be truncated. By default there's a mysql variable "group_concat_max_len" which limits the results of the GROUP_CONCAT command to 1024 characters. So if you suspect that you haven't got all the tables listed, then run something likeset group_concat_max_len=10000;before you run the command to create the DROP TABLE statement. That will override that variable just for your session and list all the table names (up to the limit you specify, of course).

If I were really clever I would turn this into a stored procedure and then I could just call that each time, specifying the database and table prefix. But I do this infrequently and I'm lazy. So... But if you figure it out, please let me know. :-)