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)
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
— 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
Atticmedia Pvt. Ltd.