40
DROP TABLE (
SELECT table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%');

I know this doesn't work! What is the equivalent for something like this in SQL? I can whip out a simple Python script to do this but was just wondering if we can do something with SQL directly. I am using MySQL. Thank you!

Kara
  • 6,115
  • 16
  • 50
  • 57
ThinkCode
  • 7,841
  • 21
  • 73
  • 92

6 Answers6

73

You can use prepared statements -

SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

It will generate and execute a statement like this -

DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3;
Damian Cehun
  • 3
  • 2
Devart
  • 119,203
  • 23
  • 166
  • 186
  • I answered in a similar way in the DBA StackExchange http://dba.stackexchange.com/a/1369/877 . +1 for Prepared Statement Usage !!! – RolandoMySQLDBA Jun 15 '12 at 15:07
  • I haven't used prepared statements thus far. Thanks for teaching me a new way to do it! – ThinkCode Jun 15 '12 at 15:10
  • I was struggling to get the DROP TABLE commands into a format MySQL could actually execute with PREPARE. This helped clear up the confusion and worked wonders :) –  Sep 07 '12 at 20:54
  • 8
    great answer! it's worth mentioning that `GROUP_CONCAT` would truncate its output if it exceeds its default value (1024, in current version), hence there might be errors during the execution of this script. See reference here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat – Ron Klein Aug 18 '13 at 13:53
  • 1
    Of course it is limited, 1024 by default. The group_concat_max_len value must be increased. – Devart Aug 17 '15 at 10:08
  • Add `SET GROUP_CONCAT_MAX_LEN=1000000;` to the top to prevent the `GROUP_CONCAT` truncation issue if your output exceeds max length. – smstl Apr 11 '22 at 15:02
  • when @tables is null it returns a mysql syntax in prepare stmt. How can i avoid it? – Hanh Nguyen May 25 '22 at 01:37
22

A minor improvement to @Devart's answer:

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.`', table_name, '`') INTO @tables FROM
(select * from
 information_schema.tables 
  WHERE table_schema = 'myDatabase' AND table_name LIKE 'del%'
  LIMIT 10) TT;

SET @tables = CONCAT('DROP TABLE ', @tables);
select @tables;
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

This script should be executed repeatedly until the console's output is NULL

The changes are:

  1. backtick (`) wrapping the table name (if it contains non standard characters)
  2. added a LIMIT to avoid the truncation issue I commented about
  3. added a "print" (select @tables;) to have some kind of control when to stop executing the script
Community
  • 1
  • 1
Ron Klein
  • 9,178
  • 9
  • 55
  • 88
  • +1 to the improvement over the original answer. But with the limit 10 means only 10 tables to process at a time? – Mohammed Joraid Jul 06 '14 at 01:20
  • @Joraid, that's right. Note the script's usage comment: "This script should be executed repeatedly until the console's output is `NULL`". That is, one should repeat it over and over because of the `LIMIT` added to it. – Ron Klein Jul 06 '14 at 07:18
  • 5
    I am getting error. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 – Narek Tootikian Feb 21 '18 at 14:59
  • Can add `SET GROUP_CONCAT_MAX_LEN=1000000;` to the top of the query to remove the need for the `LIMIT`. – smstl Apr 11 '22 at 15:02
15

If you just need to quickly drop a bunch of tables (not in pure SQL, so not directly answering this question) a one line shell command can do it:

echo "show tables like 'fsm%'" | mysql | tail +2 | while read t; do echo "drop table \`$t\`;"; done | mysql
Rafael Kitover
  • 967
  • 10
  • 13
4

I found it useful to add an IFNULL to Devart's solutions to avoid generating an error if there are no tables matching the query.

SET @tables = IFNULL(CONCAT('DROP TABLE ', @tables),'SELECT NULL;');
A C
  • 705
  • 6
  • 9
1

In addition to @Devart's answer:

If you have many tables, you may need to set group_concat_max_len:

SET group_concat_max_len = 4096;
nzy
  • 11
  • 1
-3

You can do this quickly and easily if you have phpMyAdmin available and the requirement is to drop tables with a specific prefix. Go to the database you want, and show the list of all the tables. Since tables are shown in alphabetic order, the tables with the prefix for deletion will all appear together. Go to the first one, and click the tick box on the left hand side. Then scroll down to the last table with the prefix, hold down shift, and click the tick box. That results in all the tables with the prefix being ticked. Go to the bottom of the list, and select the action drop for all selected tables. Go through with the deletion, checking that the SQL generated looks right!

mbrampton
  • 125
  • 1
  • 6