MySQL Dropping Tables

Question

What syntax for MySQL would I use to drop multiple tables that have a similar pattern to them? Something like:

DROP TABLES FROM `Database1` LIKE "SubTable*"

score 4 · Answer 1 · answered Mar 25 '11 at 08:28

Since DROP TABLE was supported by prepared statements, it can be done in this way -

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'Database1' AND table_name LIKE 'SubTable%';

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

score 3 · Answer 2 · edited May 23 '17 at 11:53

3

As noted on this question, the responses given here (Angelin and Devart) won't work in all circumstances without first increasing the limit of group_concat, as per below:

SET group_concat_max_len = 1024 * 1024 * 10;

edited May 23 '17 at 11:53

Community

1
1

answered Oct 09 '13 at 21:38

Warren Sergent

2,542
4
36
42

score 1 · Accepted Answer · answered Feb 10 '11 at 14:41

1

No. But you can select tables names from information_schema database:

select table_name
  from information_schema.tables
 where table_schema = 'Database1'
   and table_name like 'SubTable%'

And after that iterate the table names in result set and drop them

answered Feb 10 '11 at 14:41

zerkms

249,484
69
436
539

How would I iterate that using MySQL? – rolling_codes Feb 10 '11 at 22:37
It is a shame that this was marked as the correct answer, when both the other answers given actually provide methods for doing exactly as required. – Warren Sergent Oct 09 '13 at 21:39
1

@Warren Sergent - spakatak.com: there is no "something like" OP required. There is something that takes info from `information_schema` and generates queries, as I stated in my answer. You know, sometimes answers implies you need to think for a second, not just to eat free fish. – zerkms Oct 09 '13 at 22:02
thanks @zerkms i followed your train of thought and simply looped through the table names listed in the information_schema. worked like a charm – rolling_codes Jun 03 '14 at 17:37

score 0 · Answer 4 · answered Aug 23 '12 at 07:58

0

mysql> SELECT CONCAT( "DROP TABLE ", GROUP_CONCAT(TABLE_NAME) ) AS stmt

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "ur condition" into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

answered Aug 23 '12 at 07:58

Angelin Nadar

8,944
10
43
53

MySQL Dropping Tables

4 Answers4

Linked