articles >> general dba >> Track SP Changes by Automatically Saving Your ...

Track SP Changes by Automatically Saving Your SQL Server SP Text

By : Randy Dyess Jul 20, 2003

Page 2 / 3

Have you ever made the mistake of recreating an existing stored procedure without saving the old version? I have, and lucky for me I had a backup database where I could retrieve the old version when the new one failed. This mistake lead me to search for methods of keeping multiple versions of my stored procedure text around so I could be sure that I had the last few versions to track any bugs or logic problems that just suddenly showed up.

One of the easeist methods that I tried over the years is to keep text files containing the stored procedure text on a server so others could use the text as well. While this is simple to implement, it is also very easy to forget saving the file to the server and all of a sudden you are missing versions.

I also took the advice of others and looked at Visual Source Safe. This is a great product and is extremely useful if you have multiple developers working on your stored procedures in order to keep them from stepping on each other's toes and overwriting the work of others. Nice concept, but it still does not prevent others from going directly to SQL Server and creating stored procedures outside of Source Safe when they just didn't have the time or connection needed to go through Source Safe. I know this never happens where you work, and everything is done strictly to the standards and no one ever takes shortcuts while they are on a conference call with screaming clients to get something done right now, but it happens to me. Besides this, I never had luck in getting anyone to buy me any extra software.

What I did finally did was to come up with a way to let SQL Server keep backups of the text of my stored procedures for me, automatically. This way, if I forgot to keep a copy on the server, or if I worked outside of Source Safe, something would have a copy of what I did over time. I know SQL Server will version a stored procedure, but this was not what I needed. The SQL Server versioning method doesn't keep the text of the last version if someone deletes the whole thing. What I needed was something that would come in behind my back and track the text of my stored procedures, something that would not overwrite or delete anything just because I issued an ALTER PROCEDURE or DROP PROCEDURE command.

My answer was a quick and easy stored procedure that I could schedule that would save the text column of the syscomments system table. This simple stored procedure has come in handy on several occasions when I needed all versions of a stored procedure to track what changes were made over time to fix a hidden bug.

All I needed to do was to create a table in one of my system databases to keep the information in.

 

IF OBJECT_ID('tStoredProcs') IS NOT NULL
DROP TABLE tStoredProcs
GO

CREATE TABLE tStoredProcs
(
lngID INTEGER IDENTITY(1,1) PRIMARY KEY
,dtmInsertDate DATETIME DEFAULT GETDATE()
,strDatabase VARCHAR(50)
,strProcName VARCHAR(50)
,strText VARCHAR(4000)
)
GO