Enable CDC on all tables - Author: Rasmus Reinholdt Nielsen

Enable_CDC_On_All_Tables.sql

USE [master]
GO

ALTER DATABASE [{{DATABASE-NAME}}]
ADD FILEGROUP CDC;
GO

ALTER DATABASE [{{DATABASE-NAME}}] ADD FILE 
( NAME = N'{{DATABASE-NAME}}_CDC'
, FILENAME = N'{{SQL-DATA}}\MSSQL\DATA\{{DATABASE-NAME}}_cdc.MDF' 
, SIZE = 512000KB 
, FILEGROWTH = 1024KB 
) TO FILEGROUP [CDC]
GO

USE [{{DATABASE-NAME}}]
GO

EXECUTE sys.sp_cdc_enable_db;
GO

DECLARE @source_name sysname;

WHILE EXISTS ( SELECT TOP 1
                1
               FROM
                dbo.asystables A
               INNER JOIN sys.tables T
               ON
                A.table_name = T.name
                AND T.is_tracked_by_cdc = 0
               WHERE
                module <> 'SYS' )
    BEGIN
        SELECT TOP 1
            @source_name = T.name
        FROM
            dbo.asystables A
        INNER JOIN sys.tables T
        ON  A.table_name = T.name
            AND T.is_tracked_by_cdc = 0
        WHERE
            module <> 'SYS';

        EXEC sys.sp_cdc_enable_table
            @source_schema = N'dbo'
          , @source_name = @source_name
          , @role_name = N'CDCRole'
          , @filegroup_name = 'CDC';

    END;

From Rasmus Reinholdt Nielsen @ http://www.rehfeld.dk/

Do a find and replace on the {{DATABASE-NAME}} and the {{SQL-DATA}} and replace with your settings.

Might have to also change the FILENAME line depending on how you're setup.