Showing posts with label CDC. Show all posts
Showing posts with label CDC. Show all posts

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.