Tuesday, February 08, 2011

Delete All Stored Proc from SQL Server DB

If you need to delete all stored proc from sql server db, following script would be useful.

I have found this useful script from Mattberther's Blog

I have observed that his script won't delete stored procedure which has space in it.
Example: If stored procedure name is like "Category Insert" i.e. Procedure which has space in its name.

I have make line bold wherein i have add bracket to support this.

declare @procName sysname


declare someCursor cursor for
    select name from sysobjects where type = 'P' and objectproperty(id, 'IsMSShipped') = 0


open someCursor
fetch next from someCursor into @procName
while @@FETCH_STATUS = 0
begin
    exec('drop proc [' + @procName + ']')
    fetch next from someCursor into @procName
end


close someCursor
deallocate someCursor
go

1 comment:

mical said...

hey dude this is really nice blog and its very important for me and very goog described how to deliting procedure and continue posted this type of another blog and thanks alot for sharing information

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape