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/2001	DAd			Created
'
' ---------------------------------------------------------------------------------*/
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

Rate

Share

Share

Rate