Search code examples
mysqlsqldatabasesql-drop

Drop tables that are not in a list?

I need to drop about 20k tables. I know the names of the tables that are NOT to be dropped.

If I have table names "a,b,c,d,e,f,g...", how can I drop all tables that are not in the list "a,b,c"?

Solution

  • Try this to get a resultset of SQL DROP statements:

    SELECT CONCAT('DROP TABLE ', TABLE_NAME , ';')
FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='YourDatabase'
AND TABLE_NAME NOT IN ('Table1', 'Table2');

    Copy and paste the results, and you've got 20,000-n DROP statements.