Mass dropping of mysql tables

Question

Is there an application which will allow me to drop multiple mysql tables at once? I have a database that has 363 tables on it. And most of them are not needed. It would take too long if I do it in phpmyadmin since you can only drop one table at a time + the confirmation message.

Answer 1

If your the user you are connecting to the database with has "DROP TABLES" allowed then you can do it with a query...

DROP TABLE table1, table2, table3, etc, etc, etc;

Check out Mysql dropping multiple tables for a good desciption on how to do it using command line tools (if you can connect to the server via SSH).

Answer 2

For anyone else in the same situation with thousands of tables to drop, such as wordpress instances sharing one database, then this will do the job quickly but use extreme care. It will not forgive!

Run first with force=0 to show what would be dropped depending on grep filter, then change to 1 to actually do:

#!/bin/bash
# drop tables matching filter
force=0;
u=root;
p=password;
db=dbname;
filter=users_;
for t in $(mysql -u $u -p$p -D $db -Bse 'show tables' | grep $filter); do
    echo Dropping $t;
    [[ $force -eq 1 ]] && mysql -u root -p$p -D $db -Bse "drop table \`$t\`"
done