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:
I also need the name of the capture instance in the second line to get the min lsn:
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.