Category: SQL

Dropping all non-system Stored Procedures from a Database

MSCoderLeave a comment

Hello Friends,

Here in this post I am sharing SQL statements which delete all the non-system stored procedures  from SQL database.

Your suggestions and views are most welcome.

DECLARE @name VARCHAR(255)
DECLARE @SQL VARCHAR(255)

SELECT @name = (SELECT TOP 1 name FROM sysobjects WHERE [type] = 'P' AND 
category = 0 ORDER BY name)

WHILE @name is not null
BEGIN
    PRINT 'Dropping Procedure : ' + @name
    SELECT @SQL = 'Drop Procedure [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure : ' + @name
    SELECT @name = (SELECT TOP 1 name FROM sysobjects WHERE [type] = 'P' AND 
    category = 0 AND name > @name ORDER BY name)
END
GO

Happy Coding

MSCoder