Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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).

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.