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