How to delete all MySQL tables beginning with a certain prefix?

Question

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?

Answer 1

A little googling found this:

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'prefix%'

This should generate a script.

Source: Drop all tables whose names begin with a certain string

Answer 2

There's no single statement to do that.

The simplest approach is to generate a set of statements, and execute them individually.

A simple query can generate the statements for you:

 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

That just returns a rowset, but the rows conveniently contain the exact SQL statements you need to execute. (Note that information_schema is a builtin database that contains metadata. You'd just need to replace mydatabase with the name of the database you want to drop tables from.

Save the resultset from this query as a plain text file, remove any heading line, and voila, you've got a script you can execute in your SQL client.

There's no need for an elaborate stored procedure.