Dropping multiple objects with a single DROP statement
March 22nd 2009 04:16
Dropping multiple objects with a single DROP statement
----------------------------- ----------------------------- ----------------------
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.
----------------------------- ----------------------------- ----------------------
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
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.
| 40 |
| Vote |
Add To: 
