Track SP Changes by Automatically Saving Your SQL Server SP Text

Next, I simply created a simple stored procedure that I could schedule that would save the text of all current stored procedures in all the databases on the server. You could expand this include the text of views, defaults, constraints, etc., if you wanted to.

IF OBJECT_ID('spProcVersion') IS NOT NULL
DROP PROCEDURE spProcVersion
GO

CREATE PROCEDURE spProcVersion @strDBName NVARCHAR(50)
AS
SET NOCOUNT ON

--This will hold the dynamic string.
DECLARE @strSQL NVARCHAR(4000)

--Set the string
SET @strSQL = 'INSERT INTO master.dbo.tStoredProcs(strDatabase,strProcName,strText)
SELECT ''' + @strDBName + ''', so.name, sc.text
FROM ' + @strDBName + '.dbo.sysobjects so
INNER JOIN ' + @strDBName + '.dbo.syscomments sc
ON so.id = sc.id
WHERE so.type = ''p''' -Only stored procedures

--Execute the string
EXEC dbo.sp_executesql @strSQL
GO

Next, create a job and execute the above stored procedure in every database on the server.

EXEC dbo.sp_MSforeachdb @command1 = 'EXEC master.dbo.spProcVersion ''?'''

Let the job run on whatever schedule you want; create any jobs you may need to delete information over x number of days; adjust this method to use linked servers if you want; and basically adjust it to meet your needs to do what you feel is necessary to protect yourself against accidental loss of a stored procedure.

You can find out more about the undocumented stored procedure sp_MSforeachdb in my last book Transact-SQL Language Reference Guide.

Copyright 2003 by Randy Dyess, All Rights Reserved www.TransactSQL.Com