CODEWITHSUNDEEP
mysqlsql-drop
rollingcodes
rollingcodes

Reputation: 15978

MySQL Dropping Tables

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*"

Upvotes: 3

Views: 1121

Answers (4)

Warren Sergent
Warren Sergent

Reputation: 2597

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;

Upvotes: 3

Angelin Nadar
Angelin Nadar

Reputation: 9300

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;

Upvotes: 0

Devart
Devart

Reputation: 121902

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;

Upvotes: 4

zerkms
zerkms

Reputation: 254886

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

Upvotes: 1

Related Questions