SQL iterate through tables and drop if not certain name

Question

I want to drop almost all the tables in my database (9000 in total). Don't ask me why they are so many, too long to explain.

So I have 3 tables that I don't want to drop.

What SQL statement can I use to do this? I have been google-ing for a while but nothing came up!

So I have something like this in mind (in PHP):

foreach($tab as $tableList){
if($tab!='foo'&&$tab!='bar'&&$tab!='foofoobar')
mysql_query('DROP TABLE '.$tab);
}

Any ideas? even better if all could be a mysql statement!

Answer 1

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'

will give you all tables in database 'db_name'.

Don't forget to backup your tables beforing running the DROP statements.

You could use PHP to execute the DROP statements or dynamic SQL with something like this:

(copied from this question: Deleting-dynamically-managed-tables-in-mysql )

SET @v = ( SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(it.table_name) )
           FROM information_schema.tables it 
           WHERE it.table_name NOT IN ('TableToKeep_1', 'Keep_2', 'Keep_3')
             AND it.table_schema = 'db_name' ;
         ) ;
PREPARE stDropTables FROM @v; 
EXECUTE stDropTables ;