Drop All Tables Except One in MySql

Question

I have 100 tables in my database, I want to keep one only.

I tried something like below query:

DROP ALL TABLES EXCEPT my_table

But that doesn't seem to exist. any idea?

Answer 1

You can build a DROP TABLE statement with multiple listed tables, and run the query using MySQL prepared statements -

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'Database1' AND table_name <> 'my_table';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;