2

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

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
omg
  • 136,412
  • 142
  • 288
  • 348
  • If you wanted to drop all tables but not the database itself, what would you use for that? Use the same trick but exclude these ones... – Wim ten Brink Sep 21 '09 at 12:54
  • @Workshop Alex ,sorry,didn't catch you. – omg Sep 21 '09 at 12:57
  • Normally, when you want to delete all tables, you'd just delete the database. (That will also remove the stored procedures and views that might become invalid this way.) If you want to delete just the tables, you have to delete them one by one. If you want to delete all, except these two, you still have to delete them one by one but skip deletion for the ones that need to stay. – Wim ten Brink Sep 21 '09 at 13:08

2 Answers2

7

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.

derobert
  • 49,731
  • 15
  • 94
  • 124
  • This is what I meant. :-) Or something like this. – Wim ten Brink Sep 21 '09 at 13:06
  • incredibly, this deleted information_schema, and not the tables I wanted, essentially breaking my entire database. – john k Apr 29 '20 at 00:00
  • @johnktejik it should output a list of SQL statements, which ought not include tables in information_schema (unless that was your current database)... And of course, look at the output before running it – derobert Apr 29 '20 at 00:14
1

You could use mysqldump to generate a list of DROP TABLE statements, filter out the ones you don't want, then pipe it back into the mysql client. Here's how we build that up

First, here's a list of DROP TABLE table statements for the database

mysqldump -uUSERNAME -pPASSWORD--add-drop-table --no-data DATABASE| \
grep ^DROP

Now we can pipe that through grep with -v to invert the match - we want statements which don't mention the tables we're retaining (another way to do this would be --ignore-table options to mysqldump)

mysqldump -uUSERNAME -pPASSWORD--add-drop-table --no-data DATABASE| \
grep ^DROP |  
grep -v 'foo\|bar'

Finally, once you're confident, you can pipe that back into mysql

mysqldump -uUSERNAME -pPASSWORD--add-drop-table --no-data DATABASE| \
grep ^DROP | \ 
grep -v 'foo\|bar' | \
mysql -uUSERNAME -pPASSWORD DATABASE
Paul Dixon
  • 295,876
  • 54
  • 310
  • 348