Tracking Your Objects
- Getting a Count of Database Users - Part 1
- Part 2 - Automatically Gathering Database Size
- Part 3 - Automatically Getting Log Space
- Part 4 - Getting Organized
- Part 5 - A Daily Report
- Part 6 - Capturing Ad hoc Information
- Part 7 - Checking Job History
- Part 8 - Tracking Your Objects
Introduction
I have been gathering lots of information about my SQL Servers and then using this information to generate a daily report. I have chronicled my efforts in this series and the last couple columns have dealt with the reporting of this information. I am now ready to expand the types of information begin gathered with another set of stored procedures.
Object History
I try to keep fairly tight control on the schema in my databases and like to know what has changed for a few reasons. The primary reason is that I want to keep track of the changes in development and QA databases so I can build a package of objects that need to be transferred to the production databases.
In some past jobs, I had a better reason. At times there has been more than one DBA. While we communicate and try to work with each other, we have found times where changes were missed or problems introduced because one of us forgot about a change or forgot to let the others know. Prior to this we had also had problems with developers making changes in production or even QA systems to "fix" a problem. While well intentioned and sometimes needed, this type of activity usually introduces more instability and problems with most applications.
While I usually do not allow developers to act as dbo in a database ( see Keeping Developers Under Control), I do allow them to create and alter tables and procedures. Knowing that an object has changed, or that a new one was created, gives me more knowledge about the environment and state of the database. With this knowledge, my job is usually easier and I can provide better support for everyone.
The Solution
If you have read my other articles, then you know I hate to write code that uses unsupported or undocumented methods of solving a problem. This is mainly because I have had code break during upgrades and had to rewite processes because Microsoft changed the way an unsupported function worked. The code that I have written using supported methods has continued to work through upgrades.
Having repeated this, I examined the method that I used in v6.5 to find out which objects had changed. As you may remember, the only way to get a list of objects in a database was to query the sysobjects table. With SQL 7, we got a series of ANSI standard views that list the tables, views, and stored procedures, but we also got the ALTER command.
The problem with the INFORMATION_SCHEMA views is that they do not display the date an object was created or altered. In addition, the ALTER command allows you to change the definition of an object without changing it's object_id, it's permissions, etc. Unfortunately, there is also no date stamp in sysobjectsthat can tell you when an object was altered.
So what can we do? I emailed a few people that I know and dug into documentation and found there is no way to know when an object was altered. At least no way that I have been able to unearth. So I was left with examining the system tables and trying to determine that a change had been made.
After quite a bit of experimentation, I finally found that I actually can use sysobjects to track changes. I hate doing it and this is the second unsupported item I have written about. I apologize in advance, but here is what I implemented.
While examining the sysobjects table in SQL 2000, I found there are two fields that are changed when an object is altered. These fields are the base_schema_ver and the schema_verfields. This is an int field that is incremented by 16 each time an object is altered. It starts at 0 and then moves to 16 on the first alter, 32 on the next, etc. Checking books online shows that base_schema_ver is marked as reserved, but schema_ver is defined. I chose to use schema_verin my queries to track the chanes to objects.
The code I wrote uses some of the techniques that I have presented in earlier articles in this series as well as some of the objects. Specifically, I reuse the DBALog table for tracking the information about changed objects. For the tracking, I use the following objects:
- CurrObj
- dbspLoadCurrObj
- dbspChkCurrObj
- DBALog - presented in Part 6
Each of these objects is described below as part of the process that I use to track object changes.
Implementation Details
Now for the technical part. To implement the tracking, I first needed a place to store my reference data. This is the last known value for each object. One technique I have used in the past is to build a table that I will update periodically with the last known values. I chose to create the CurrObj table, which holds the basic information I need from sysobjects to track changes.
The second item I needed was a process to load the current values from sysobjects into this table. The dbspLoadCurrObj stored procedure performs this task. I use a DBA database to contain all my stored procedures and objects, so this stored procedure actually loops through all databases (except samples) and loads the information from sysobjects in each database into the CurrObjtable. Of course, it clears this table first.
The third item needed for this process is a method to check for changes. The dbspChkCurrObj procedure performs this check. It is similar to dbspLoadCurrObj in that it loops through all databases, but this time the current information in each sysobjects table is compared with the information stored in CurrObj. Any differences are recorded in the DBALog table.
The final item needed is not provided as code because it is a job. The job needs to be scheduled along with any other items that you are using to gather information about the server. This job should run the dbspChkCurrObj procedure and then the dbspLoadCurrObj procedure. This way the checks for new objects will be run first and then the CurrObj table will be loaded with the curent status.
Note: You will need to run dbspLoadCurrObj manually when you set this up or the first day all objects in all databases will appear as changes.
Conclusions
One limitation of this system is that I am not tracking the actual changes themselves. I assume that everyone has a version control system of some sort (which you should) and uses this to store the actual code. This code can be compared against the current version on the server to find out what has changed. This system is only intended as an alert system that tells me something changed. I still have to spend time tracking down exactly when the change occurred, what it was, and who did it.
You will also need some method that reports on the entries in DBALog. You can use the one presented in an earlier article (Part 6) or develop your own.
I know that this system has come in handy for me in keeping control of my databases and I hope you find it useful as well. Again, I apologize for the use of an unsupported feature in my code.
As always, I welcome feedback on this article and please take a few minutes to rate it below.
Steve Jones
© dkRanch.net March 2001