3

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"?

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
llaskin
  • 789
  • 2
  • 9
  • 24

7 Answers7

3

You can get a list with this

SELECT CONCAT("DROP TABLE ", table_name, ";") 
FROM information_schema.TABLES 
WHERE table_schema = <whatever your db name is> 
AND table_name NOT IN (<your list>);

Then copy and paste!

Johan
  • 74,508
  • 24
  • 191
  • 319
josh.trow
  • 4,861
  • 20
  • 31
2

You need to run select from information_schema.tables and create a cursor that will iterate through the results and perform the appropriate drops (use if statement). This is because drop statement does not support selecting/filtering or other options. (unless something has changed during the last two or three years)

When you're performing the select statement you can use something like:

WHERE table_name NOT IN ('ssss','dddd');

Another thing is: why do you have 20k tables in your database?????

kubal5003
  • 7,186
  • 8
  • 52
  • 90
  • I know now - you have lots of similar tables separated by client. It makes everything easier and all the security audit people much happier. – josh.trow May 03 '11 at 20:36
2

I would suggest that you take the following approach

  • extract a full list of all of the tables one table per line with SHOW TABLES (or from information_schema.TABLES)
  • open the list in a text editor
  • remove the tables that you don't want to remove from the list
  • use a macro or search/replace to turn each line into a DROP TABLE command

Now you have an SQL script that you can run against the database

If you're a linux shell ninja might want to use commandline tools like uniq, xargs, etc to manipulate the files. Using a spreadsheet might be another way to deal with it.

James C
  • 14,047
  • 1
  • 34
  • 43
2

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.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
0

I ran into this exact same problem today. Here is one approach for skipping tables ABC, DEF and XYZ:

mysql -Nu USER --password=PASSWORD DATABASE -e 'show tables' | \
perl -ne 'BEGIN { print "SET FOREIGN_KEY_CHECKS=0;\n" };
    !/^(ABC|DEF|XYZ)$/ && s/^(.*)/DROP TABLE `\1`;/ && print' | \
mysql -u USER --password=PASSWORD DATABASE

So what the devil is all that?

  1. The first mysql call gets all the tables without the header row (-N option)
  2. The output of mysql is piped to Perl
  3. The Perl script adds a first line to skip foreign key checks since we are dropping the tables
  4. The script then adds DROP TABLE X for each table, skipping the tables ABC, DEF and XYZ
  5. The output from Perl goes to mysql, modifying the same database
CharlieH
  • 1,432
  • 2
  • 12
  • 19
0

There is a much easier way. In one line, on your command line type:

mysql -u root -p[password] --skip-column-names [database] -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='[database]' AND TABLE_NAME NOT IN ('[table 1]', '[table 2]',..'[table n]');" | cut -f1 | sed -r 's/(.*)/DROP TABLE IF EXISTS \1;/' | mysql -u root -p[password] [database]

You may get two warnings about including passwords in a command. If this may be a problem, you can clear the command history to 'remove the evidence' :-)

history -c
Al Zziwa
  • 1,127
  • 9
  • 5
0

I'm going to suggest a completely different approach if you can get away with the tables that you want to keep being made unavailable for a short period of time.

  • Backup the tables that you want to keep
  • DROP DATABASE the database with all of the tables in
  • Restore the tables that you want to keep from the backups
James C
  • 14,047
  • 1
  • 34
  • 43