DELIMITER $$
CREATE PROCEDURE drop_tables_like(pattern VARCHAR(255), db VARCHAR(255))
BEGIN
SELECT @str_sql:=CONCAT('drop table ', GROUP_CONCAT(table_name))
FROM information_schema.tables
WHERE table_schema=db AND table_name LIKE pattern;
PREPARE stmt from @str_sql;
EXECUTE stmt;
DROP prepare stmt;
END$$
DELIMITER ;
For dropping all tables starting with 'a' in 'test1' database you can run:
CALL drop_tables_like('a%', 'test1');
Reference: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%';
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
It will generate and execute a statement like this -
DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3;