SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL 2000: Track Stored Procedure Changes

By Satish More,


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.


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.

Total article views: 1233 | Views in the last 30 days: 1
Related Articles

SSIS - Reusable Benchmark Harness

We all know testing is important, but face it, testing is not the highlight of anyone's daily work. ...


Benchmark SQL Server Disk Latency

Typically, I am a big advocate of performance monitor but one place I commonly see performance monit...


Read the New TPC Database Benchmarking Series

Introduction to TPC Database Benchmarks Let’s talk about database application benchmarking. Thi...


Alter A Partitioned Table Column DataType.

Alter A Datatype of a Table which is Partitioned.


Some Intel Westmere Benchmarks

PCLab.pl has an interesting review and benchmarks of a 3.07GHz Westmere-EP processor (which is the s...