-- First enable CDC at database level.
USE MyDb
GO
EXEC sys.sp_cdc_enable_db
GO
-- Then enable CDC for all the tables in the database (excluding sys table and tables that starts with '_')
BEGIN
IF OBJECT_ID('tempdb..#tempTables') IS NOT NULL DROP TABLE #tempTables
SELECT
Row = ROW_NUMBER() OVER (ORDER BY t.Name),
t.Name
INTO #tempTables
FROM
sys.tables t LEFT JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
type='U' and -- only user tables
t.Name NOT LIKE '[_]%' and -- ignore table that have a name that starts with '_'
t.Name NOT LIKE 'sys%' and -- ignore table that have a name that starts with 'sys'
s.name = 'dbo' and -- only dbo tables
t.is_tracked_by_cdc = 0 -- exlcude tables where cdc is already enabled
ORDER BY Row
Declare @row int = 0
Declare @tableName nvarchar(50)
SELECT @row=Row, @tableName=Name FROM #tempTables WHERE Row=1
WHILE @tableName IS NOT NULL
BEGIN
print 'Enabling cdc for table ' + @tableName
EXEC ('EXEC sys.sp_cdc_enable_table @source_schema = N''dbo'', @source_name = N''' + @tableName + ''', @role_name = NULL')
SELECT @row=min(Row) FROM #tempTables WHERE Row>@row
SELECT @tableName=Name FROM #tempTables WHERE Row=@row
END
IF OBJECT_ID('tempdb..#tempTables') IS NOT NULL DROP TABLE #tempTables
END
