Dropping multiple SQL Server objects with a single DROP statement

Dropping multiple SQL Server objects with a single DROP statement

Written By: Atif Shehzad -- 3/19/2009 -- 0 comments

Stay informed - get the MSSQLTips.com newsletter and win - click here

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 probalby 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.

Script # 1: Create 6 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 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.

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

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.

Script # 3: Drop USP4, USP5, USP6 through single drop statement

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.