Technical Article

Generate Drop Script for all Objects of a Type

,

Generates a series of drop statements for all objects of the input type (e.g., 'U' for User Tables).

Install in the master database.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DropObjectsScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DropObjectsScript]
GO


/* ---------------------------------------------------------------------------------
' Stored Procedure:  sp_DropObjectsScript
'
' Author:  Dan duLuard
'
' Description:  Creates a drop script for all objects of input type.  
'
' Parameters:
'@piObjectType:  subset of values from sysobjects.xtype domain.
'@piFilter:  optional object name filter.  
'
' Date      By         Notes
' ------------------------------------------------------------------------
' 08/14/2001DAdCreated
'
' ---------------------------------------------------------------------------------*/create proc sp_DropObjectsScript (
@piObjectType char(2),
@piFilter sysname = null
)
as
set nocount on

declare @ObjectTypeName sysname

select @ObjectTypeName = 
case @piObjectType 
when 'TR' then 'trigger' 
when 'U' then 'table'
when 'P' then 'proc'
when 'V' then 'view'
else null
end

select @ObjectTypeName

if @ObjectTypeName is null 
begin
raiserror ('Object type not supported.',16,1)
return(-1)
end

select 'drop ' + @ObjectTypeName + ' ' + name
from sysobjects
where xtype = @piobjecttype
and objectproperty(id,'IsSystemTable')=0
and name <> 'dtproperties'
and name like isnull(@pifilter, name) + '%'
order by name

Read 2,171 times
(11 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating