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

3 Answers 3

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

Use the mysql command line.

If all tables which need to be dropped share a common prefix like ("phpbb_", use this command:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'phpbb_%';

This generates the SQL command which needs to be run to actually get rid of them.

share|improve this answer

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

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.