SQL: deleting tables with prefix

Question

How to delete my tables who all have the prefix myprefix_?

Note: need to execute it in phpMyAdmin

Answer 1

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_%';