Enable Change Data Capture (CDC) in your SQL server database

-- 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