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_%";