SqlScript for dropping a set of procedures, user-defined functions or views
I have been wanting to write some posts lately of some productivity tools and tricks. My last post was for a VS-Addin for updating the reference paths for all "selected" projects in your visual solution set.
This post is for a sql script that enables you to drop a series of stored procedures, UDF's or Views. I thought about adding tables but then I would have to handle keys/constraints as well....maybe next time.
PRINT N'Dropping procedures/functions...'
GO
PRINT N''
GO
declare @name varchar(256),
@type varchar(2), @sql
nvarchar(1000), @printText
varchar(1000)
-- declare cursor
declare localCursor
cursor local fast_forward
read_only for
select [name], [type]
from [sysobjects]
where [type] in ('P','TF','FN','V')
and (
[name]
in
(
'[Put procedure/view or udf name here]',
'[Put another procedure/view or udf name here]'
)
)
order by [type],[name]
-- now open the cursor
open localCursor
fetch next from localCursor into @name,
@type
-- now loop through building list.
while @@fetch_status=0
begin
if( @type =
'P' )
begin
set @printText =
'Dropping procedure ' +
@name +
' ...'
set @sql =
'drop procedure ' +
@name
end
else if ( @type
in ('TF','FN') )
begin
set @printText =
'Dropping function ' +
@name +
' ...'
set @sql =
'drop function ' +
@name
end
else if ( @type
in ('V') )
begin
set @printText =
'Dropping view ' +
@name +
' ...'
set @sql =
'drop view ' +
@name
end
print @printText
exec sp_executesql
@sql
fetch next from localCursor into @name,
@type
end
close localCursor
deallocate localCursor
HTH,
-Mathew Nolton