MySQL: how to drop multiple tables using single query?

Question

I want to drop multiple tables with ease without actually listing the table names in the drop query and the tables to be deleted have prefix say 'wp_'

Similar question - http://stackoverflow.com/questions/4958424/mysql-question-on-dropping-tables/5430104#5430104 — Devart, Jul 20 '11 at 11:07

John P · Answer 1 · 2014-02-06T19:11:38.740

I've used a query very similar to Angelin's. In case you have more than a few tables, one has to increase the max length of group_concat. Otherwise the query will barf on the truncated string that group_concat returns.

This is my 10 cents:

-- Increase memory to avoid truncating string, adjust according to your needs
SET group_concat_max_len = 1024 * 1024 * 10;
-- Generate drop command and assign to variable
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE 'my_table%';
-- Drop tables
PREPARE str FROM @dropcmd; EXECUTE str; DEALLOCATE PREPARE str;

Thank you for this - without the group_concat_max_len increase, Angelin's answer would never have worked for my scenario. — Warren Sergent, Oct 09 '13 at 21:34

Angelin Nadar · Accepted Answer · 2012-04-11T07:54:46.020

9

Just sharing one of the solutions:

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;

edited Apr 11 '12 at 07:54

answered Jul 20 '11 at 07:48

Angelin Nadar

8,944
10
43
53

score 4 · Answer 3 · edited Jan 13 '14 at 09:19

4

Simple solution without risk of error:

mysqldump create a file that contains DROP command like

DROP TABLE IF EXISTS `wp_matable`;

a 'grep' with "DROP TABLE wp_" give us the commands to execute

so drop is made by theses trhee lines (you can edit drop.sql to check which tables would be dropped before)

mysqldump -u user -p database > dump.sql 
grep "DROP TABLE `wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql

edited Jan 13 '14 at 09:19

Digital_Reality

4,488
1
29
31

answered Jan 13 '14 at 08:57

marlboro51

41
1

I am not sure if there is an error in your answer but to make the grep command work, I had to write `DROP TABLE IF EXISTS` instead of just `DROP TABLE` and I had to escape the backtick with the "\" character. For example: `grep "DROP TABLE IF EXISTS \\`table_prefix_" dump.sql > drop.sql`. I did the export simply with `sudo mysqldump > dump.sql` and then logged in as root through Unix socket with `sudo mysql` before selecting the database with `use db_name` and executed the SQL file with `\. drop.sql`. – baptx Apr 20 '20 at 11:14

score 2 · Answer 4 · edited Mar 07 '16 at 18:25

2

Be careful with "_", need to be written with "\" before in Mysql like:

SELECT CONCAT('DROP TABLE',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE '**my\\_table**%';

edited Mar 07 '16 at 18:25

Angelos Chalaris

6,611
8
49
75

answered Mar 07 '16 at 17:02

Funkyhead

21
1

score 2 · Answer 5 · answered Oct 04 '21 at 19:14

A less complicated solution when a large number of tables are needed to be deleted -

SELECT GROUP_CONCAT(table_name SEPARATOR ", ")
    -> AS tables
    -> FROM information_schema.tables
    -> WHERE table_schema = "my_database_name"
    -> AND table_name LIKE "wp_%";
+-------------------------------------------------------------------------
| tables              
+-------------------------------------------------------------------------
| wp_t1, wp_t2, wp_t3, wp_t4, wp_t5, wp_t6, wp_t7, wp_t7, wp_ .......... 
+-------------------------------------------------------------------------

Copy the table names. Then use -

DROP TABLE 
    -> wp_t1, wp_t2, wp_t3, wp_t4, wp_t5, wp_t6, wp_t7, wp_t7, wp_ ..........;

score 0 · Answer 6 · answered Oct 12 '15 at 15:52

0

For the great mysqldump solution it's better to use the option --skip-quote-names

mysqldump --skip-quote-names -u user -p database > dump.sql 
grep "DROP TABLE wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql

You get rid of backticks in table names. The grep part won't work in some enviroments with the backticks.

answered Oct 12 '15 at 15:52

darkone_vt777

1

You can make the backtick work with grep if you escape it with the character "\": https://stackoverflow.com/questions/6758652/mysql-how-to-drop-multiple-tables-using-single-query#comment108480150_21087169 – baptx Apr 20 '20 at 11:19

score -3 · Answer 7 · answered Jul 18 '16 at 13:18

-3

Go to c:\xampp\mysql\data\your folder

Select multiple tables that you want remove and then press delete button

Thanks

answered Jul 18 '16 at 13:18

Sohail Iqbal

15
2

2

This makes the assumption that the original poster is using XAMPP and has it installed to that directory. – Jeremy Harris Jul 18 '16 at 19:53

score -4 · Answer 8 · answered Sep 02 '15 at 03:23

-4

Dropping single table in mysql:

DROP TABLE TABLE_NAME;

answered Sep 02 '15 at 03:23

krishnachaitanya

1
3

MySQL: how to drop multiple tables using single query?

8 Answers8

Linked

Related