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
- The first resultset will display the objects created (recreated or new) after the benchmark date
- The second resultset will display the objects modified using ALTER statement after the benchmark date
- The third resultset includes the objects which were dropped and then recreated (DROP and CREATE) with some changes after the benchmark date
- the fourth resultset includes the objects which are dropped after the bechmark date.
