Dropping all non-system Stored Procedures from a Database


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s