How to automate the Stored procedure and Function cleanup process for your database

Posted: April 13, 2011 in Uncategorized

SELECT Name, xtype INTO DeleteDBItemTable FROM sysobjects WHERE Xtype IN (‘P’, ‘FN’, ‘TF’)
SELECT * FROM DeleteDBItemTable
WHILE EXISTS(SELECT TOP 1 1 FROM DeleteDBItemTable)
BEGIN
DECLARE @name NVARCHAR(2000)
DECLARE @xtype NVARCHAR(2000)
DECLARE @command NVARCHAR(2000)
SELECT TOP 1 @name = Name , @xtype = xtype FROM DeleteDBItemTable
IF(@xtype = ‘P’) SELECT @command = ‘DROP PROCEDURE ‘ + @name
ELSE SELECT @command = ‘DROP FUNCTION ‘ + @name
EXEC sp_executes @command
DELETE FROM DeleteDBItemTable WHERE Name = @name AND xtype = @xtype
END
DROP TABLE DeleteDBItemTable

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