Technical Article

SQL 2000: Track Stored Procedure Changes

,

Introduction

Care about SQL 2000?Yes/No...well atleast I know some engagements still going on SQL Server 2000. So how would you come to know if somebody modifies a database stored procedure code using ALTER statement and hasnt communicated to you. It is possible to find this out in SQL 2005. You've got DDL triggers, modify_date column of sys.objects...all these in SQL 2005. But we do not have any such option in SQL Server 2000.

But this was very essential for me because firstly my project was on SQL 2K and secondly there used to be some ad hoc changes on Production for some unknown but urgent reason which if not communicated to Dev would not be tracked into the Version system. If the object is dropped and then recreated then fine...that can be traced by a daily job which would check for the crdate column in sysobjects. But the ALTER event is not logged in any of the system tables.The consequence..the changes get overwritten in further releases from Dev. That raises the need for a manual script, rather a job which would track any changes that are made in the database code either by ALTER or by CREATE or by DROP.

Implementation

The script makes use of the syscomments system table which has the latest code.

The first execution of the script will get the contents of the syscomments table in to the TrackProcChanges table and also the date when the contents are copied. Lets refer to this date as the benchmark date. On next execution (when you want to check whether there are any modifications),the contents of syscomments are then compared to the contents in TrackProcChanges table (benchmark code).

After giving out the output the script refreshes the TracProcChanges table with the latest code from syscomments table and also adds the current date as the new benchmark date

Interpretation of output

  1. The first resultset will display the objects created (recreated or new) after the benchmark date
  2. The second resultset will display the objects modified using ALTER statement after the benchmark date
  3. The third resultset includes the objects which were dropped and then recreated (DROP and CREATE) with some changes after the benchmark date
  4. the fourth resultset includes the objects which are dropped after the bechmark date.
-- Objects created (new and recreated) after the specified dateIF object_id('SysCommentsArchiveLog') IS NOT NULL
SELECT * FROM sysobjects 
WHERE crdate >= (SELECT TOP 1 LogDate FROM SysCommentsArchiveLog ORDER BY LogDate desc)
 AND xtype IN ('TR','FN','TF','V','IF','P')

IF object_id('TrackProcChanges') IS NOT NULL
BEGIN
-- Objects modified (using ALTER) after the specified date using ALTER  
SELECT object_name(A.id) AS ObjectName,A.[text] AS CurrentText,B.[text] AS OlderText 
 FROM syscomments A
 JOIN TrackProcChanges B
 ON A.id = B.id
 AND A.number = B.number
 AND A.colid = B.colid
 AND A.[text] <> B.[text]

-- Objects recreated (using DROP and CREATE) after the specified date 
 SELECT object_name(A.id) AS ObjectName,A.[text] AS CurrentText,B.ObjectName,B.[text] AS OlderText
 FROM syscomments A
 JOIN TrackProcChanges B
 ON object_name(A.id) = B.ObjectName
 AND A.number = B.number
 AND A.colid = B.colid
 AND A.[text] <> B.[text]
 JOIN sysobjects C
 ON A.id = C.id
 WHERE C.xtype IN ('TR','FN','TF','V','IF','P') AND C.crdate >= (SELECT TOP 1 LogDate FROM SysCommentsArchiveLog ORDER BY LogDate desc)

-- Objects dropped after the specified date
 SELECT B.ObjectName,A.[text] AS CurrentText,B.ObjectName,B.[text] AS OlderText
 FROM syscomments A
 RIGHT JOIN TrackProcChanges B
 ON object_name(A.id) = B.ObjectName
 WHERE A.id IS NULL
END

IF object_id('TrackProcChanges') IS NOT NULL DROP TABLE TrackProcChanges
IF object_id('SysCommentsArchiveLog') IS NULL CREATE TABLE SysCommentsArchiveLog (LogDate datetime NOT NULL)
INSERT SysCommentsArchiveLog SELECT getdate()
SELECT A.id,B.Name AS ObjectName,A.number,A.ColId,A.[text]
 INTO TrackProcChanges 
 FROM syscomments A 
 JOIN sysobjects B ON A.id = B.id 
WHERE B.xtype IN ('TR','FN','TF','V','IF','P')

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (2)

You rated this post out of 5. Change rating