MySQL bulk drop table where table like?

mysql sql-drop

ThinkCode · Jun 15, 2012 · Viewed 59.9k times · Source

DROP TABLE (
SELECT table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%');

I know this doesn't work! What is the equivalent for something like this in SQL? I can whip out a simple Python script to do this but was just wondering if we can do something with SQL directly. I am using MySQL. Thank you!

Answer

You can use prepared statements -

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;

MySQL bulk drop table where table like?

Answer

Related questions