Dropping multiple objects using a single DROP statement

Today, let's see how we can drop multiple objects with single statement.

Almost every SQL Server object that is created may need to be dropped at some time, especially when you are developing. You create a bunch of temporary objects which you do not want to keep in the database for long. Most SQL Server users drop one object at a time using either SSMS or a drop statement. In many scenarios we may need to drop several objects of the same type. Is there a way to drop several objects through less lines of code?

With T-SQL we can drop multiple objects of the same type through a single drop statement. Almost any object that can be dropped in a single drop statement can also be dropped simultaneously with other objects of the same type through one drop statement.

Some of these include objects like databases, tables, functions, stored procedures, rules, synonyms etc.

Let's look at an example.

First we create a few stored procedures, so we can test single and multiple drops.

Script # 1: Create 4 stored procedures

USE AdventureWorks
GO
CREATE PROCEDURE USP1
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO
CREATE PROCEDURE USP2
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO
CREATE PROCEDURE USP3
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO
CREATE PROCEDURE USP4
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO

Now we have 4 stored procedures to work with.

Let's drop the first three using a single drop statement as shown below.

Script # 2: Drop USP1, USP2, USP3 through three drop statements

USE AdventureWorks
GO
DROP PROCEDURE USP1
DROP PROCEDURE USP2
DROP PROCEDURE USP3
GO

Let's create USP1, USP2 and USP3 again.

USE AdventureWorks
GO
CREATE PROCEDURE USP1
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO
CREATE PROCEDURE USP2
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO
CREATE PROCEDURE USP3
AS
BEGIN
SELECT TOP 10 * FROM Person.Address
END
GO

The following script will drop multiple stored procedures through one drop statement. We can see that we just need to put the list of objects to drop and separate them with a comma. as shown below. The rest of the syntax is the same.

Script # 3: Drop USP1, USP2, USP3 through single drop statement
USE AdventureWorks
GO
DROP PROCEDURE USP1,USP2,USP3
GO

Through Script # 3 USP1, USP2 and USP3 have been dropped in a single drop statement.

Following are some of the pros and cons of multiple object drops:

Benefits

The multiple objects drop approach is applicable to all versions of SQL Server.

If some objects in the list do not exist or can not be dropped due to privileges or they do not exist, the remaining objects will be successfully dropped without any negative impact.

Although no query plan is generated for drop statements, you can see the dropping of multiple objects approach consumes less bytes while requesting data over the network. This can be verified from network statistics while client statistics are enabled in SQL Server Management Studio (SSMS).

Through less lines of code you can get more done.

Short Comings

It is not possible to apply pre-existence check for the objects you want to drop, such as IF EXISTS

It should be obvious, but good to mention that you can not drop objects of different types together in a single statement. For example you can not drop tables and stored procedures at the same time.

Hope this way we can reduce our statements and reduce network traffic.