MySQL: How to drop all tables starting with a prefix?

In my database there is a lot of tables starting with _elgg , now I want to drop all tables with this prefix. Can anyone give me a solution ?

Thanks in advance!

First do a

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name';

and check if this returns the correct statements for every table. If you want to execute those statements, do

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '\_elgg%' 
AND TABLE_SCHEMA = 'your_database_name' 
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

Another way: On the command line, to do it in one rush, you can do

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`

StackExchange Database Administrators Q#56804, answer score: 13