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.
Thanks!
Exactly what I needed.
Sometimes you will encounter to drop table many tables with lengthy names and it will be possible to excess the default length of group_concat can return . You can just edit the config file of mysql or make use of the function below to work-around this:
function drop_tables($host , $user , $password , $dbname , $prefix = ”)
{
$con = mysql_connect($host , $user , $password );
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}
$array = array();
$sql = “SELECT table_name
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘$dbname’ “;
if(empty($prefix))
$sql .= “AND TABLE_NAME LIKE ‘%%’”;
else
$sql .= “AND TABLE_NAME LIKE ‘$prefix%’”;
$result = mysql_query($sql,$con) or die( mysql_error());
while($row = mysql_fetch_assoc($result))
{
$array[] = $row['table_name'];
}
$sql = “DROP table “.implode(“,” , $array);
mysql_close($con);
return $sql;
}
If you have shell access try this simple script:
create a ~/.my.cnf with:
[client]
user = dbuserid
password = dbpassword
host = mysql_server
[mysql]
database = dbname
Now create the following shell script:
drop_tables.sh
#!/usr/bin/tcsh
if ($#argv != 1) then
echo “Usage: $0 table_prefix”
echo e.g. $0 wp
exit 1
endif
foreach i ( `mysql -e “show tables like ‘$1_%’” | grep -v Tables_in_ ` )
echo mysql -e “drop table $i”
mysql -e “drop table $i”
end
exit 0
Handy for clearing out joomla and wordpress installs
Hi,
if you can use php try this:
$delete_array = mysql_query(“SHOW TABLE STATUS LIKE ‘x__%’”);
while ($delete = mysql_fetch_array($delete_array)) {
mysql_query(“DROP TABLE ” . $delete['Name']);
}
greetz
Rene