Monthly Archives: October 2019

SQL server find all the cdc related databases and tables

DECLARE @sql as varchar(max)

SET @sql = ‘Select @@SERVERNAME DB_SERVER, NULL AS DB_NAME, NULL AS Table_name, NULL AS CDC_table_name, Null As Create_Date’

SELECT @sql=@sql+’ UNION ALL select @@SERVERNAME DB_SERVER, ”’ + name + ”’ AS DB_Name, COLLATE DATABASE_DEFAULT AS table_name
, cdc.capture_instance + ”_CT” as CDC_table_name

FROM ‘ + name + ‘.sys.tables sys(NOLOCK) JOIN ‘ + name + ‘.cdc.change_tables cdc (NOLOCK) on sys.object_id = cdc.source_object_id’ +char(10)
FROM sys.databases
WHERE is_cdc_enabled = 1

EXEC (@sql)