Drop All Tables Except One in MySql

Question

I have 100 tables in my database, I want to keep one only.

I tried something like below query:

DROP ALL TABLES EXCEPT my_table

But that doesn't seem to exist. any idea?

score 15 · Accepted Answer · answered Apr 27 '13 at 11:26

15

You can build a DROP TABLE statement with multiple listed tables, and run the query using MySQL prepared statements -

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'Database1' AND table_name <> 'my_table';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

answered Apr 27 '13 at 11:26

Devart

119,203
23
166
186

Super piece of code - and combined with IN () you can keep multiple tables too – Antony Mar 24 '17 at 15:37
@Devart how to check for null for variable tables – Ruchita Sheth Aug 01 '17 at 10:13

score 4 · Answer 2 · answered Apr 27 '13 at 10:53

4

You can't drop multiple tables in MySQL.

The easiest in your case would be to export the table you want to keep (using a tool like mysqldump), then drop and recreate the database.

answered Apr 27 '13 at 10:53

Wander Nauta

18,832
1
45
62

My table is so large i don't want to export it. It would be then easier to drop each table alone. – ARMAGEDDON Apr 27 '13 at 10:55
1

Then you should take a look at this question: http://stackoverflow.com/questions/1454328/is-there-a-mysql-command-to-implement-something-like-drop-tables-except-t1-b2 – Wander Nauta Apr 27 '13 at 10:56

score 1 · Answer 3 · edited Apr 27 '13 at 11:57

1

I wonder why no one suggested to go to the directory :

C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\data\user_db\...

and delete them easily there? this is what I did and it seems working

edited Apr 27 '13 at 11:57

Prahalad Gaggar

11,389
16
53
71

answered Apr 27 '13 at 11:31

ARMAGEDDON

939
3
11
23

1

It would work, if all the tables would be MyISAM tables. If the database has InnoDB tables, then it wouldn't be that easy... – GregD Apr 27 '13 at 12:48

score 1 · Answer 4 · edited May 23 '17 at 12:00

1

If you have lots of tables, watch out for truncation by the GROUP_CONCAT function. It has a setting for maximum length of the returned string: group_concat_max_len

See: GROUP_CONCAT reference manual entry

This answer guided me to the solution: MYSQL: How to define or get LONG string variables

edited May 23 '17 at 12:00

Community

1
1

answered Dec 11 '13 at 17:18

G. Stevens

115
3
5

score 0 · Answer 5 · answered Apr 27 '13 at 11:11

You can build all drop statements output them info file and use file after that

SELECT CONCAT( 'DROP TABLE ', table_name , ';' ) AS statement FROM information_schema.tables WHERE table_name not like 'table_to_keep' and table_schema like 'your_schema' into outfile 'path_to_file;
source path_to_file;

score 0 · Answer 6 · answered Apr 27 '13 at 11:48

0

You can use mysqldump , try it !

mysqldump -u[USERNAME] -p[PASSWORD]--add-drop-table --no-data [DATABASE] |
grep ^DROP |
grep -v 'my_table' |
mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

answered Apr 27 '13 at 11:48

zey

5,939
14
56
110

Drop All Tables Except One in MySql

6 Answers6

Linked

Related