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

Well many times i have to clean up all my stored procedures and the functions from the database. When i use sql server Generate script wizard to generate SP and FUNCTION script it does not add drop script on the top.

So either i have to Change all create to Alter to make all the SP overwritten on my live server or i have to keep a change log. However if i will do this i will change all newly created SP to alter as well, which will give me SP does not exists on the server. I do not want that and i also i don’t want to keep a change change, i always want to generate scripts from staging server and want to push these to live.

You can use below script to drop all the SP’s and FUNCTION’s from your database.

— this will create DeleteDBItemTable automatically, which will be dropped at the end
SELECT Name, xtype INTO DeleteDBItemTable FROM sysobjects WHERE Xtype IN (‘P’, ‘FN’, ‘TF’)

— you can see here what does the table got
SELECT * FROM DeleteDBItemTable

— loop through and delete all the SP and FUNCTIONS
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_executesql @command
    DELETE FROM DeleteDBItemTable WHERE Name = @name AND xtype = @xtype
END

— drop the table, it is now no more needed.
DROP TABLE DeleteDBItemTable

In the above query you see Xtype IN (‘P’, ‘FN’, ‘TF’) , here

P stands for Procedure

FN stands for FUNCTION

TF stands for TABLE FUNCTION

TARUN JUNEJA

Atticmedia Pvt. Ltd.

Noida

Advertisements

One thought on “How to automate the Stored procedure and Function cleanup process for your database

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