SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Server DB Changes Scripting

    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?

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •