Saturday, September 3, 2011

Delete all stored procedures of a database in sql server

Sometimes there arises the need of deleting all stored procedures of a database. I came across the same situation when I was generating CRUD operations stored procedures via third party tool. I once changed a table and then had to regenerate all the stored procedures. So went on to find out the way. And ultimately stumbled at http://ctrlf5.net/?p=164. Here is how to delete all the stored procedures of a sql server database:
USE myDBName
GO
DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur
All the best! kick it on DotNetKicks.com

0 comments:

Post a Comment

Hope you liked this post. You can leave your message or you can put your valuable suggestions on this post here. Thanks for the sharing and cooperation!

Popular Posts

Recent Articles