SQL iterate through tables and drop if not certain name

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 d开发者_如何学Co 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!

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 ;

You can drop more then one table at once using comma.

     DROP TABLE table_1, table_2, etc;

from you PHP code, i think you should be using OR not AND...

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

for obvious reason.

well, if you want to do it within MySQL,

you can, list the all the tables first... using

show tables > whateverfile.txt

and store in a file... edit the file as needed... i.e. remove names of tables that should not be deleted and add DROP TABLE to the first line of the file, and execute it within MysQL... mysql < whateverfile.txt....