MySQL – How to drop all tables starting with a prefix?

Solution

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'prefix%' AND TABLE_SCHEMA = 'my_database'
INTO outfile '/tmp/drop_my_tables.sql';

SOURCE /tmp/drop_my_tables.sql;

Explanation

The SELECT statement builds another statement, DROP, that is written to an output file. Then the output file is taken by the SOURCE command that executes its (SQL) content.

Example

Let’s consider we have a database school having the following tables:

• course
• student
• student_course_association
• tmp_student_activity1_association
• tmp_student_activity2_association

And we want to remove all the tables starting with tmp. The code will look like this:

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'tmp%'
    AND TABLE_SCHEMA = 'school'
INTO outfile '/tmp/drop_tmp_school_tables.sql';

SOURCE /tmp/drop_my_tables.sql;

The SQL script drop_tmp_school_tables.sql will be:

DROP TABLE school.tmp_student_activity1_association;
DROP TABLE school.tmp_student_activity2_association;

So after running the SOURCE command the two DROP statements are executed and the school.tmp_student_activity1_association and the school.tmp_student_activity2_association tables will be removed.