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_'
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.
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.
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.
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;
Just sharing one of the solutions:
mysql> SELECT CONCAT( "DROP TABLE ",
GROUP_CONCAT(TABLE_NAME) ) AS stmtFROM 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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With