CODEWITHSUNDEEP
mysqlsqlmultiple-tablesdrop-table
Krunal
Krunal

Reputation: 2203

Drop multiple tables in one shot in MySQL

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.

Upvotes: 162

Views: 232319

Answers (4)

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102448

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.

Upvotes: 227

Javaughn Jackson
Javaughn Jackson

Reputation: 31

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.

Upvotes: 3

OrangeDog
OrangeDog

Reputation: 38826

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.

Upvotes: 104

user4774666
user4774666

Reputation: 1

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

Upvotes: -3

Related Questions