MS SQL Server DB Changes Scripting

[JimBolla]

Is is possible using MS SQL Server to programmatically iterate through the transaction logs to determine all the changes made to any of the tables in a database?

We have an application that automatically updates itself by checking home for updates on a regular basis. Part of this upgrade process includes the database. The upgrade needs to update their database without deleting any data. Any ideas?

[MattR]

I don't think the transaction log is meant to be human readable, so I don't think it could happen. What I would do is either create another table which contains the tableID and a datestamp (then place a trigger on the tables which either update or create a new timestamp in the changelog table) or add a datestamp column to the table and then a history table of 'last checked' or the like.

[JimBolla]

well i'm not just looking to track table changes but also changes to sprocs, views, and UDFs (user defined functions). i thought perhaps there was a way to programmatically read the transaction log files using SQL com objects perhaps.

[MattR]

You didn't say that the first time!

I don't know of a way to iterate thru the x-act log (what about if it is dumped b4 you can read it?) but I'd query the system tables to find this.

I left my big poster of system tables and their relationships @ work, but if I recall correctly you can do something like this:

SELECT name,
       crdate,
  CASE type
       WHEN 'P' THEN "Stored Procedure"
       WHEN 'U' THEN "User Table"
       WHEN 'D' THEN "Index"
       -- etc.
  END
  FROM sysobjects
 WHERE type != 'S' -- don't need to know for system tables

Of course, if you wish to actually track what changed and not just when they changed you're going to have to do something else. I guess what I'd do is something like this:

CREATE TABLE audit( 
  objectid    int      not null,
  dateupdated datetime not null,
  contents    text     not null,
)

And store the stored procedure definition in there and perform a check every so often and if the date/contents differ create a new row. Same thing for views and such.