MySQL – How to drop all tables starting with a prefix?
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;
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.
Let’s consider we have a database school having the following tables:
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.