Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Dropping multiple SQL Server objects with a single DROP statement


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

Attend a SQL Server Conference for FREE >> click to learn more


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.

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, April 28, 2012 - 12:16:37 AM - Atif Back To Top

I would suggest to simply complete the task in two step.

First, Generate the comma seperated list of tables that start with B and are required to drop

select name+', '
from sys.objects
where type = 'U'
and name like 'M%'

 

Then analyze the names and put the comma seperated result in drop statement.

It would simplyfy the task and you would have option to analyze the names of tables that you are going to drop.

Thanks

 


Friday, April 27, 2012 - 6:11:15 AM - jenifer Back To Top

I would like to drop more then one tables using a single sql statment.

Need to drop the tables that are start with B

like Drop Table bill like 'B%'

 

so here i would like to drop the tables that are starts with B with the single SQL statement. Can any one help me out with this?


Learn more about SQL Server tools