151

How to drop multiple tables from one single database at one command. something like,

> use test; 
> drop table a,b,c;

where a,b,c are the tables from database test.

Deepak Rai
  • 2,163
  • 3
  • 21
  • 36
Krunal
  • 2,061
  • 3
  • 13
  • 13
  • 24
    you already answer yourself – ajreal Feb 07 '11 at 14:32
  • according to the answers below, in HeidiSQL you can filter tables by their name (upper textbox), write `DROP TABLE ` into a query and double click each desired table to append its name to the query (put a comma between them) then hit F9 to execute. A bit off-topic but I came here for that. – Ivan Ferrer Villa Feb 21 '17 at 12:45
  • For people wanting to drop several tables with the same prefix, since `DROP TABLE table_prefix_*` does not work with the star character: https://stackoverflow.com/questions/6758652/mysql-how-to-drop-multiple-tables-using-single-query#comment108480150_21087169 – baptx Apr 20 '20 at 11:26

4 Answers4

209

We can use the following syntax to drop multiple tables:

DROP TABLE IF EXISTS B,C,A;

This can be placed in the beginning of the script instead of individually dropping each table.

AntoineB
  • 4,535
  • 5
  • 28
  • 61
Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
  • 35
    Maybe it's worth pointing out that the tables don't need to have any relationship at all. They can be completely independent and this syntax will still work. – crmpicco Feb 19 '14 at 10:11
  • 2022, gives syntax error in `,`. Will post a solution if I find one. – Sid Nov 22 '22 at 02:59
  • @Sid works fine for me on MySQL 5.7, make sure you use backticks "`" to surround names – Volatil3 Dec 09 '22 at 12:04
98
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS a,b,c;
SET foreign_key_checks = 1;

Then you do not have to worry about dropping them in the correct order, nor whether they actually exist.

N.B. this is for MySQL only (as in the question). Other databases likely have different methods for doing this.

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
  • 5
    You've saved me from lots of hassle by turning of fkey checks (y). – HungryCoder Jul 11 '14 at 03:18
  • I still get **# 1451 - Deleting or updating a parent record fails due to a foreign key restriction** Edit: i was running this in phpmyadmin, and the checkbox for foreign key checks was still on. I think you dont need the `SET foreign_key_checks` query there, just use the checkbox. – Black Jul 20 '21 at 14:10
1

A lazy way of doing this if there are alot of tables to be deleted.

  1. Get table using the below

    • For sql server - SELECT CONCAT(name,',') Table_Name FROM SYS.tables;
    • For oralce - SELECT CONCAT(TABLE_NAME,',') FROM SYS.ALL_TABLES;

  2. Copy and paste the table names from the result set and paste it after the DROP command.

Javaughn Jackson
  • 11
  • 2
-3
declare @sql1 nvarchar(max) 
SELECT @sql1 =
  STUFF(
         (
           select ' drop table dbo.[' + name + ']'

           FROM sys.sysobjects AS sobjects
           WHERE (xtype = 'U') AND (name LIKE 'GROUP_BASE_NEW_WORK_%')
           for xml path('')
        ),
     1, 1, '')

  execute sp_executesql @sql1
Vikrant
  • 4,920
  • 17
  • 48
  • 72
user4774666
  • 1