Dropping multiple SQL Server objects with a single DROP statement

By: Atif Shehzad   |   Read Comments (2)   |   Related Tips: More > T-SQL

Problem

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 that you probably do not want to keep in the database long term.  Most SQL Server users drop one object at a time using either SSMS or a single 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? And what types of SQL Server objects can be dropped simultaneously through a single drop statement?

Solution

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, user defined functions, stored procedures, rules, synonyms etc..., but to examine the syntax and details of such drop statements we will go through a simple example using stored procedures.

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

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
CREATE PROCEDURE USP5 AS BEGIN SELECT TOP 10 * FROM Person.Address END 
GO
CREATE PROCEDURE USP6 AS BEGIN SELECT TOP 10 * FROM Person.Address END 
GO

Now we have 6 stored procedures to work with.

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

USE AdventureWorks
GO
DROP PROCEDURE USP1
DROP PROCEDURE USP2
DROP PROCEDURE USP3
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.

USE AdventureWorks
GO
DROP PROCEDURE USP4,USP5,USP6
GO

Through Script # 3 USP4, USP5 and USP6 have been dropped in a single drop statement.

Following are some benefits and short comings 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.

Next Steps

• In cases when you do not check for pre-existence of an object before the drop, then you can drop multiple objects in a single drop statement
• This is another good tip to put in your toolbox to help you drop objects much quicker with less code.

About the author

Atif is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips

We Recommend

• More Database Developer Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code