Friday, November 18, 2016

Query for Change Data Capture (CDC) tables and columns

Real quickly, I was in need of a query that would give me a listing of the tables and columns involved in Change Data Capture (CDC). I wanted to see the tracked columns in one column to the right of the table name so I utilized FOR XML PATH. Below is what I came up with.

SELECT OBJECT_NAME(source_object_id) [Table Name],
      (SELECT name + ',' FROM sys.columns SC WHERE SC.object_id = CT.object_id AND name NOT LIKE '__$%' FOR XML PATH('')) [Columns],
 capture_instance,
 supports_net_changes,
 filegroup_name
 FROM CDC.change_tables CT


It's certainly not perfect but it should give you a good start if you are ever in need of such a query.