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?

Accepted Answer

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;
```

Drop All Tables Except One in MySql

Solution

Problem

Related problems