Is there a MySQL command to implement something like "drop tables except t1,b2"?

mysql, sql-drop

Solution

You can use `information_schema` to find table names, and even format the results as a bunch of `DROP` statements.

SELECT CONCAT('DROP TABLE ', TABLE_NAME, '; ')
  FROM information_schema.tables
  WHERE table_schema = DATABASE() AND table_name NOT IN ('foo', 'bar', 'baz');

(The `DATABASE()` function returns the currently `use`'d database.)

Using `PREPARE` and `EXECUTE`, you could even avoid copy & paste, and (in MySQL 5.0.13 and later) write a stored procedure to do this.

Problem

I want to keep t1,t2 and drop all other tables.

Original source