PHP Magazine

If you’ve ever installed some 3rd party software on you website (eg: phpbb forum), you certainly know that database tables, created during that process, generally have a convenient prefix in their names (default one for phpBB is phpbb_). This is done, I believe, to make sure that all of application’s tables get grouped together and do not mix with your previously created ones.

There are situations when you’d want to remove such grouped tables, all of them at once – for example, when, several days ago, I obtained our latest UMapper SQL-dump I, unfortunately, forgot to ignore forum tables, so they got into my localhost box. Today, I spotted those and wondered how can I quickly get rid of them. As I really like to work with MySQL via CLI, the question raised – how to remove multiple tables using single query.

At first, I tried to use LIKE clause the way it is used in SHOW TABLES:

mysql> SHOW TABLES LIKE "phpbb_%"; /* list all phpBB tables */
mysql> DROP TABLES LIKE "phpbb_%"; /* DROP THEM? Quite a naive approach.. */

Well, even before executing it I was 99% sure that this “method” wouldn’t work. Indeed, it didn’t

I certainly knew that you can drop several tables at once if you list all of them separated with commas:

mysql> DROP TABLE phpbb_zebra, phpbb_words; /* DROP several tables*/

So, I decided that it would be good enough if I form a query which would generate yet another query that would drop all similarly prefixed tables. If it’s not already, it would be crystal clear what I mean in a minute.

To get list of tables complying to certain criteria one could use information_schema database. To get list of all phpbb_ prefixed tables we can issue something like this:

mysql> SELECT GROUP_CONCAT(TABLE_NAME) 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Result (line-breaks added for readability):

phpbb_acl_groups,phpbb_acl_options,phpbb_acl_roles,phpbb_acl_roles_data,
phpbb_acl_users,phpbb_attachments,phpbb_banlist,phpbb_bbcodes,phpbb_bookmarks,
SKIPPED
phpbb_search_results,phpbb_search_wordlist,phpbb_search_wordmatch,phpbb_sessions,
phpbb_sessions_keys,phpbb_sitelist,phpbb_smilies,phpbb_styles,phpbb_styles_imageset,

Going from here, the only thing we need is to prepend the result with DROP TABLE string:

mysql> SELECT 
           CONCAT(
                  "DROP TABLE ",  
                   GROUP_CONCAT(TABLE_NAME)
           ) AS stmt 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Then, actually removing tables is a matter of copy-pasting the generated query.

That’s it! If you know a better way, please, share in comments!

P.S. It only occurred to me that I relied on two queries for doing the job, albeit second one was simply copy-pasted.