from - MySQL: how to drop multiple tables using single query?
teradata drop multiple tables (5)
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_'
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**%';
Dropping single table in mysql:
DROP TABLE TABLE_NAME;
Go to c:\xampp\mysql\data\your folder
Select multiple tables that you want remove and then press delete button
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
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;
Simple solution without risk of error:
mysqldump create a file that contains
DROP command like
DROP TABLE IF EXISTS `wp_matable`;
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