Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: how to drop multiple tables using single query?

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_'

like image 757
Angelin Nadar Avatar asked Jul 20 '11 07:07

Angelin Nadar


People also ask

Can you Drop multiple tables at once SQL?

Example 2: Drop multiple tables together using the SQL DROP Table statement. We can drop multiple tables together using a single DROP Table statement as well.

How do I delete multiple tables in MySQL?

From the manual: You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join.

How do you Drop multiple tables with common prefix in one query?

Here is my solution: SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;') FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%'; And of course you need to replace TABLE_PREFIX_GOES_HERE with your prefix.


2 Answers

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;
like image 141
John P Avatar answered Oct 01 '22 13:10

John P


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;

like image 39
Angelin Nadar Avatar answered Oct 01 '22 12:10

Angelin Nadar