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
DROP TABLE WHERE TABLE_NAME LIKE 'aggregate_temp%';
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.