2

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.

Improve this question

2 Answers 2

Reset to default
6

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;
Improve this answer
0
2

object_id() function is your friend:

declare @capture_instance sysname select @capture_instance = capture_instance from cdc.change_tables where source_object_id = object_id('dbo.myTable)
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.