SQL: deleting tables with prefix

Question

Hello,

i know how to delete my tables in MySQL,

DROP TABLE

but now i need to know how to delete my tables who all have the prefix

myprefix_

How to?

--I NEED TO DO THIS IN PHPMYADMIN, IN A BROWSER. NO TERMINAL STUFF PLEASE. BATCH STUFF IS ALWAYS APRECIATED.--

Answer 1

You can do that in one command with MySQL:

drop table myprefix_1, myprefix_2, myprefix_3;

You'll probably have to build the table list dynamically in code though.

An alternative approach would be to use the general purpose routine library for MySQL 5.

Answer 2

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

In the MySQL shell or through PHPMyAdmin, use the following query

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

This will generate a DROP statement which you can than copy and execute to drop the tables.

EDIT: A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';