Wednesday, December 28

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

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty