Generate Drop Script for all Objects of a Type

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