Drop Multiple Tables with One DROP TABLE Statement

You can drop multiple tables with one DROP TABLE statement by listing the table names, separated by commas. The statement is shorter than three separate ones. It has one rule about order, and one surprise about errors.

One Statement, Several Tables

Many developers write one DROP TABLE for every table. You can drop multiple tables in one statement, because the syntax accepts a list. Name each table, put a comma between the names, and SQL Server removes them in the order you wrote them. The demo creates three staging tables first, then removes all of them with a single statement.

IF DB_ID(N'DropTablesDemo') IS NULL CREATE DATABASE DropTablesDemo;
GO
USE DropTablesDemo;
GO
DROP TABLE IF EXISTS dbo.StageOrders, dbo.StageCustomers, dbo.StageProducts;
CREATE TABLE dbo.StageOrders (ID int NOT NULL PRIMARY KEY, Item nvarchar(40) NOT NULL);
CREATE TABLE dbo.StageCustomers (ID int NOT NULL PRIMARY KEY, FullName nvarchar(60) NOT NULL);
CREATE TABLE dbo.StageProducts (ID int NOT NULL PRIMARY KEY, Title nvarchar(60) NOT NULL);
SELECT name FROM sys.tables ORDER BY name;

DROP TABLE dbo.StageOrders, dbo.StageCustomers, dbo.StageProducts;
SELECT COUNT(*) AS TablesLeft FROM sys.tables;

All three tables are gone. The list can hold as many tables as you need, and the tables can belong to different schemas. Cleanup scripts that create several staging tables benefit most from this form. The cleanup becomes one line at the end of the script.

Put the same line at the top of a script, with IF EXISTS, and the script can run twice. It no longer fails on the tables of the last run.

Foreign Keys Decide the Order

The order of the list matters when the tables are linked. A table that holds a foreign key must come before the table it points to. SQL Server can’t remove a parent while a child still references it. The next script builds a small parent and child, plus an unrelated table.

CREATE TABLE dbo.Customers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE dbo.Orders (
    OrderID    int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (CustomerID)
);
CREATE TABLE dbo.Notes (NoteID int NOT NULL PRIMARY KEY);

Now list the parent first. This is the wrong order.

DROP TABLE dbo.Customers, dbo.Orders, dbo.Notes;

Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Customers' because it is referenced by a FOREIGN KEY constraint.

SELECT name FROM sys.tables ORDER BY name;

The error names Customers, and the table is still there. The result lists only Customers, so Orders and Notes are gone. Without TRY and CATCH, SQL Server reports the error for the failing table. It still drops the rest of the list. A multi-table drop is not all or nothing, so check the result after any error.

The right order lists Orders before Customers. The IF EXISTS form, available in SQL Server 2016 and later, adds a second benefit. It skips tables that don’t exist, so the same line works on a database that never created some of them. The permission rule is the same as for a single drop. You need ALTER permission on the schema, or CONTROL permission on each table.

DROP TABLE IF EXISTS dbo.Orders, dbo.Customers, dbo.Notes;
SELECT COUNT(*) AS TablesLeft FROM sys.tables;

Inside a TRY Block

A TRY block changes the behavior. The next script creates the parent and child again and drops the parent first inside TRY. The CATCH block returns the error number, and the last query lists the tables that remain.

CREATE TABLE dbo.Customers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE dbo.Orders (
    OrderID    int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (CustomerID)
);
GO
BEGIN TRY
    DROP TABLE dbo.Customers, dbo.Orders;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
SELECT name FROM sys.tables ORDER BY name;
GO
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;

The first error stops the statement. Nothing after it is dropped, so both tables remain. The two behaviors differ, and both are easy to forget. They are a good reason to list the tables in the right order every time.

Drop Tables by Name Pattern

Some scripts create work tables with a shared prefix, such as Tmp_. They need to remove all of those tables at the end. Read the names from the catalog and build one DROP TABLE statement. The script below creates three prefixed tables and one table to keep. It then builds the statement with STRING_AGG, shows it, and runs it.

CREATE TABLE dbo.Tmp_LoadA (ID int);
CREATE TABLE dbo.Tmp_LoadB (ID int);
CREATE TABLE dbo.Tmp_Totals (ID int);
CREATE TABLE dbo.KeepMe (ID int);
GO
DECLARE @sql nvarchar(max) = (
    SELECT N'DROP TABLE IF EXISTS '
         + STRING_AGG(QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name), N', ')
         + N';'
    FROM sys.tables
    WHERE name LIKE N'Tmp[_]%');
SELECT @sql AS GeneratedStatement;
EXEC (@sql);
SELECT name FROM sys.tables ORDER BY name;

QUOTENAME wraps each name in brackets, so odd names can’t break the statement. The LIKE pattern uses a bracket for the underscore, because an underscore alone matches any character. STRING_AGG needs SQL Server 2017 or later. Always print the generated statement before you run it on a real database.

Temporary Tables Work the Same Way

A list also works for temporary tables. The next script creates two and drops both in one statement. The check shows that none is left.

CREATE TABLE #ScratchOne (ID int);
CREATE TABLE #ScratchTwo (ID int);
DROP TABLE #ScratchOne, #ScratchTwo;
SELECT COUNT(*) AS ScratchLeft FROM tempdb.sys.tables WHERE name LIKE N'#Scratch%';

You could argue that three separate statements are easier to read, and that a failing one is easier to find. That holds for a long script of different jobs. For a block of scratch tables that belong together, one line says what it means. You also run it once.

What to Remember

To drop multiple tables, list the referencing table before the table it references. Add IF EXISTS when a table can be missing. Check the table list after an error, because the rest of the list is still dropped. Generate the statement from the catalog when the tables share a name pattern, and print it first.

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'DropTablesDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DropTablesDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DropTablesDemo;
END;

A DROP statement is not a list of chores, it is a list of consequences.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.