Sql-server – How to retrieve the Capture Instance for a given table

change-data-capturesql server

Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled.

I am trying to write a generic function that returns the CDC history information for a give type including the relevant information from all base tables.

Supposing my type is User with the table dbo.Users which inherits directly from dbo.DataObjects, my query should look something like this:

DECLARE @Begin_LSN binary(10),@End_LSN binary(10) 
SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Users') 
SELECT @End_LSN = sys.fn_cdc_get_max_lsn() 
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_DataObjects(@Begin_LSN, @End_LSN,'ALL') AS a 
JOIN cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_LSN, @End_LSN,'ALL') AS b 
ON a.__$start_lsn = b.__$start_lsn
WHERE ID = 10;

This will give me the complete history trail for the User with ID 10 including the columns from the base table.

Now I want to build this kind of query dynamically.

One of my problems is that the cdc functions to retrieve the history information include the name of the capture instance: 

cdc.fn_cdc_get_all_changes_<capture_instance>

I also need the name of the capture instance in the second line to get the min lsn:

sys.fn_cdc_get_min_lsn('<capture_instance>')

The capture instance name either gets created automatically for me when I enable CDC for a table or is passed manually. If I don't want to keep track of the capture instance name myself, is there a way to retrieve the name of the capture instance for a given table?

I am aware that there can be more than one capture instance per table (max. 2 I think), but I likely always will only use one per table.

PS: I am also aware that the capture instance name is usually built in the way <schema>_<table>, but relying on this assumption seems a bit risky. The naming convention could change for future versions, or someone could supply a manual capture instance name / change the name.

Best Answer

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;
Related Question