Have you ever wondered what tables have recently been changed in your database? What about the store procedures, views, and/or other objects that have changed? How about not only those objects that have changed but those that have been deleted?

I suppose those of you that have wondered about changed objects have gone to Enterprise Manager (EM) and looked at the “Create Date” column to determine what has changed. This column is useful to identify when an object has been created, but it doesn’t help identify those object that have only been altered.

Knowing what has changed in your environment can give you valuable information when trying to determine why an application has stopped working or is working differently then expected. Understanding the changes that have been implemented will help you narrow down the objects that might be causing the problems. Also in a development environment where over time many object are changed it might be nice to get a report of all objects changed since a given date. This report could then be used to build a script to copy objects that have changed into your production environment. For these reasons I developed a process to audit changes to SQL Server objects. This article will describe in detail how this process works.

The Process

The process I built consists of a SQL Server table, “object_versions”, to log object changes, a stored procedure (SP), “usp_objects_changed”, to identify the changes, and a SQL Agent job, “What has Changed”, that has a reoccurring schedule for when the SP looks for changes. Prior to reviewing the actual stored procedure that tracks objects that change, lets review the key information I used in the sysobjects table that allowed me to build this object audit process.

Each database in SQL Server has a “sysobjects” table. This table contains information about each objected stored in the database. If you review the information in books online (BOL) about the “sysobject” table, you will find a couple of columns that are essential in determine when objects change, these columns are “schema_ver” and “crdate”.

The “schema_ver” column is a integer column with the following BOL definition: “Version number that is incremented every time the schema for a table changes”. This column is key to identifying when existing objects are changed. The “schema_ver” column starts out with a value of 1 when the object is created. However when you modify an existing table, view, index, SP, default, and/or constraint the value of the “schema_ver” column changes. The “schema_ver” column value always gets larger every time the object is changed.

The “crdate” column is a datetime field that identifies the date and time the object was initially created. This date gets established the first time you create the object, and does not change when you modify the object.

Therefore by using “crdate” and “schema_ver” together you can uniquely identify each different version of a table, view, SP, trigger, index, and constraint. The process I wrote takes advantage of these above two “sysobjects” columns, to help track changing over time, to all objects stored in each database within a given SQL Server box.

Now that you understand the “sysobjects” columns my process uses lets review the SP I wrote to track object changes.

SQL Server Table to Log Object Changes

Not all the information I needed was contained in the “sysobjects” table. The “sysobjects” table was only valuable to track new objects. I wanted to track deleted, changed, and re-instated objects. Therefore I created a SQL Server table “object_versions” to track object changes over time. The “object_versions” table was created using the script in web listing 1.

The definition and usage of each column the “object_versions” table can be found in Table A, below. As you can see from web listing 1, the primary key on the “object version” table is a composite key consisting of “dbname”, “name”, “schema_ver”, “version_date”, and “uid” columns.

The “schema_ver” column is 1 ever time an object is created, even when it is deleted and recreated. Due to this fact I needed a way to distinguish the difference between a brand new object , and a dropped and recreated object. This is why I also include the “version_date” as part of the primary key, so I could uniquely identified objects even it they have been dropped and recreated.

Each object, in each database, contains at least one records in this table. If an object has been changed, then each change is tracked by creating a new record in the table for the changed object.

The Stored Procedure

The “usp_objects_changed” SP processes through each database comparing the information in the “sysobjects” table, with information in the “object_versions” table to determine if an object has been added, changed, deleted, re-instated (deleted, and recreated), or restored to a prior version. If the SP determines that an object has changed since the last running of the report then an email message is sent to the DBA’s reporting all changes. Lets review this stored procedure in a little more detail section by section. The SP can be found in web listing 2.

The SP’s first section “Section A” is the section that defines and initializes variables and creates a temporary table for usage by the rest of the SP. This section builds a global temporary table “##temp_table”. This table is used to hold lines for the report which will be emailed to the DBA’s at the end of this SP. A global temporary table is used so that it can be populated from within a dynamic command executed by the “sp_MSforeachdb” SP. If it where a normal temporary table it would not be accessible from the “sp_MSforeachdb” SP because this SP is processed in a different session and temporary tables are only available to the same session that created the temporary table. This section starts building the email report lines by populating the global temporary table with three lines. These three lines make up the report and column headings.

The next section, “Section B”, builds and executes code to populate the “##temp_table”. A single “INSERT” command is used to populate the report lines to be inserted into “##temp_table”. The “INSERT” command is built and placed in the @CMD local variable. The command joins the “sysobjects” and “sysusers” table with the “object_versions” table and then dynamic generates all the report lines to be inserted. A row is inserted if a new, changed, reinstated, deleted, or restored objects is found in a database. Each object identified will have a different version then the current object record in the “object_versions” table. A version is identified by the columns “crdate” and “schema_ver” in the “sysobjects” table. The command stored in variable @CMD is run against each database by using the “sp_MSforeachdb” SP.

Between section B and C there is a test to determine whether any objects have been changed. To identify if any objects have changed this test checks to see if the “##temp_table” has more than 3 records (the header records). If more than three records exist in this temporary table then the rest of the sections are processed.

Section C adds a record into the “object version” table for each new or changed object. The record inserted identifies the specific version of the object, based on “crdate” and “schema_ver” columns in the “sysobjects” table. By keeping track of each version of an object, this SP is able to identify not only new and/or changed objects, but also deleted, restored, or re-instated objects. Once again an INSERT command is built and placed in variable @CMD, which is executed against each database using the “sp_MSforeachdb” SP which does the actual insertion of records into the “object_versions” table.

If modules have been deleted, then section D is used to update a “dflag” in the “object_versions” table. The “dflag” column is used to identify objects that have been deleted from the database. This flag is set to 1, on all records for a object that has been deleted. This bit field is used to help identify when objects are re-instated (deleted, and re-created). Section D builds the “UPDATE” command needed to set the “dflag” for any modules that has been deleted. This command is place in the @CMD variable and executed against each database, once again using the “sp_MSforeachdb” SP.

Section E, the last section, notifies the DBA’s of all the objects changed. This is done by using xp_sendmail, and sending the DBA’s the results by selecting all the “email_text” columns from the ##temp_text global temporary table. The output of the “SELECT” statement basically looks like a report. Below you will find an example of a typical report:

Scheduling of the SP

We schedule this SP to run nightly, Monday through Friday using a SQL Server agent job named “What has Changed”. The job contains a single step. That step contains a T-SQL command to execute “usp_objects_changed” SP. Having the SP and the SQL Agent job allows the DBA’s to get a report daily of changed modules.

Identifying Modules that have been changed between two Different Dates

There is another benefit we have obtained from having the historical information about objects that have changed. We are now able to identify modules that have changed overtime in our development environment. Being able to identify this allows a project to quickly identify, which objects they need to promote from our development environment to our production environment.

The “object_versions” table is used to identify objects that have changed overtime. Here is some code that can be used to report the modules that have changed overtime. This sample code identifies all the user tables that changed in the month of December 2002, deleted tables are excluded from the selection.

When this code is run against my sample “object_versions” table the report below is displayed. This output shows that the Orders table was changed 6 times, the customer table was changed twice, and the Items and Inventory tables where changed only once.

Of course if you were building a script to modify your production table, you would need to know not only the changed tables, but also the deleted tables. Here is some sample code to display tables that were deleted in December 2002.

Here is the results from the above select statement:

The “object_versions” table not only identifies tables that have been changed, but also identifies any other type of object that has changed. Basically the “object_versions” table tracks any object that SQL Server stores in the “sysobjects” table. Review definition of “xtype” above to identify all objects tracked.

Conclusion

Before we started routinely running the “usp_objects_changed” SP we had no way of knowing exactly when objects where changed on any one of our databases. Now with this process we are able to know each morning what objects where changed during the prior business day. This monitoring gives us a heads up on what modules might cause application issues. This heads up allows us to speed up diagnosing database problems more quickly by allowing us to know what has changed.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a website that contains a number of SQL Server examples to common DBA and SQL developer issues.