Sql-server – Find changes made in the table in sql server 2012

Input (hopefully stored procedure parameters):

DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

Code:

DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1), 
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * 
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a 
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b 
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql, 
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;

Firstly,

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.