How to delete all MySQL tables beginning with a certain prefix?
mysql, sql
Solution
There's no single statement to do that.
The simplest approach is to generate a set of statements, and execute them individually.
We can write a simple query that will generate the statements for us:
SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'`;') AS stmt
FROM information_schema.tables t
WHERE t.table_schema = 'mydatabase'
AND t.table_name LIKE 'aggregate\_temp%' ESCAPE '\\'
ORDER BY t.table_name
The SELECT statement returns a rowset, but each row conveniently contains the exact SQL statement we need to execute to drop a table. (Note that `information_schema` is a builtin database that contains metadata. We'd need to replace `mydatabase` with the name of the database we want to drop tables from.
We can save the resultset from this query as a plain text file, remove any heading line, and voila, we've got a script we can execute in our SQL client.
There's no need for an elaborate stored procedure.
Problem
I've found another thread on this question, but I wasn't able to use its solutions, so I thought I'd ask with more clarity and detail. I have a large MySQL database representing a vBulletin forum. For several years, this forum has had an error generated on each view, each time creating a new table named `aagregate_temp_1251634200`, `aagregate_temp_1251734400`, etc etc. There are about 20,000 of these tables in the database, and I wish to delete them all. I want to issue a command that says the equivalent of `DROP TABLE WHERE TABLE_NAME LIKE 'aggregate_temp%';`. Unfortunately this command doesn't work, and the Google results for this problem are full of elaborate stored procedures beyond my understanding and all seemingly tailored to the more complex problems of different posters. Is it possible to write a simple statement that drops multiple tables based on a `name like` match?