Detect changes in SQL Server database

Question

I need to know if a SQL Server 2000 database has been changed. The changes are not important, just to know if any change has been made. Maybe SQL Server keeps a timestamp or an incremental field that provide that information for internal uses. Has any one idea about it? Thanks...

Answer 1

Just put a delete/insert/update trigger on every table and log every operation to a central location. Something like this (untested):

CREATE TRIGGER dbo.auditTableName
ON dbo.TableName
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N'dbo.TableName', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            'Update' ELSE 'Insert' END ELSE 'Delete' END;
END
GO

You can generate this code easily, like this, assuming all objects are under dbo:

SELECT 'CREATE TRIGGER dbo.audit' + name + '
 ON dbo.' + name + '
 FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N''dbo.' + name + ''', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            ''Update'' ELSE ''Insert'' END ELSE ''Delete'' END;
END
GO' FROM sysobjects WHERE type = 'U';

Again, that's just a general idea, untested as I don't have SQL Server 2000 handy. Use results to text and you should get the appropriate trigger for each table.

Answer 2

One option is timestamp (rowversion sql 2005+).

It would only apply to tables that include the timestamp data type so this may be prohibitive if trying to retrofit a database.

timestamp

@@DBTS

rowversion (Transact-SQL)

CREATE TABLE ExampleTable (
    PriKey int PRIMARY KEY
    , timestamp)

select @@DBTS

insert into ExampleTable(PriKey) values (1)

select @@DBTS