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.
资源下载链接为: https://pan.quark.cn/s/3d8e22c21839 随着 Web UI 框架(如 EasyUI、JqueryUI、Ext、DWZ 等)的不断发展与成熟,系统界面的统一化设计逐渐成为可能,同时代码生成器也能够生成符合统一规范的界面。在这种背景下,“代码生成 + 手工合并”的半智能开发模式正逐渐成为新的开发趋势。通过代码生成器,单表数据模型以及一对多数据模型的增删改查功能可以被直接生成并投入使用,这能够有效节省大约 80% 的开发工作量,从而显著提升开发效率。 JEECG(J2EE Code Generation)是一款基于代码生成器的智能开发平台。它引领了一种全新的开发模式,即从在线编码(Online Coding)到代码生成器生成代码,再到手工合并(Merge)的智能开发流程。该平台能够帮助开发者解决 Java 项目中大约 90% 的重复性工作,让开发者可以将更多的精力集中在业务逻辑的实现上。它不仅能够快速提高开发效率,帮助公司节省大量的人力成本,同时也保持了开发的灵活性。 JEECG 的核心宗旨是:对于简单的功能,可以通过在线编码配置来实现;对于复杂的功能,则利用代码生成器生成代码后,再进行手工合并;对于复杂的流程业务,采用表单自定义的方式进行处理,而业务流程则通过工作流来实现,并且可以扩展出任务接口,供开发者编写具体的业务逻辑。通过这种方式,JEECG 实现了流程任务节点和任务接口的灵活配置,既保证了开发的高效性,又兼顾了项目的灵活性和可扩展性。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值