How to Enable Change Data Capture (CDC) On Entire Table OR Enable CDC On Table With List Of Columns in SQL Server

Scenario: 

Sometime we have requirement to Enable CDC on Table including all the columns OR enable CDC on table with set of columns. The SP can perform both tasks depending upon provided parameters.This Stored Procedure will be used when CDC is already not enabled on Table. If Change Data Capture (CDC) is already enabled on Table , The Stored Procedure will not perform any action.

Solution: 

The below stored Procedure can be used to enable CDC on all the columns of a table or any chosen columns.

USE [DatabaseName]
GO

/*-----------------------------------------------------------------------------------------------------
How to Execute: EXEC usp_EnableCdcOnTableWithOrWithoutColumnList @pSchemaName,@pTableName,@pColumnList

Enable CDC on Table with All columns
Example :usp_EnableCdcOnTableWithOrWithoutColumnList 'SchemaName','TableName',NULL

Enable CDC on Table with Given columns
Example :usp_EnableCdcOnTableWithOrWithoutColumnList 'SchemaName','TableName','Col1,Col2'
---------------------------------------------------------------------------------------------------------*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Usp_enablecdcontablewithorwithoutcolumnlist] 

@pSchemaName VARCHAR(50),--> Provide the Schema Name where table exists

@pTableName  VARCHAR(100),--> TableName to ENABLE CDC ON.

@pColumnList VARCHAR(1000)--> ColumnList eg.'col1,col2'

AS
  BEGIN
      --DECLARE LOCAL VARIABLES

      DECLARE @vSQLStatment NVARCHAR(MAX)
      DECLARE @vSQLEnableCDC NVARCHAR(MAX)
      DECLARE @vXML XML;
      DECLARE @vCDCEnableInd INT
      DECLARE @vColumnCount INT

      -- IF CDC ALREADY ENABLED

      SET @vCDCEnableInd=(SELECT is_tracked_by_cdc
                          FROM   sys.tables
                          WHERE  name = @pTableName)

      -- CHECK IF CORRECT TABLE NAME IS PROVIDED

      IF NOT EXISTS (SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS
                     WHERE  TABLE_NAME = @pTableName)
        BEGIN
            PRINT ' The given table does not exists in Database::'
                  + @pTableName
        END
      -- ENABLE CDC WITH ALL THE COLUMNS IF NO COLUMNS PROVIDED

      ELSE
        BEGIN
            IF @pColumnList IS NULL
               AND @vCDCEnableInd = 1
              BEGIN
                  PRINT 'CDC is alrady enabled on ::' + @pTableName
                      
              END

            IF @pColumnList IS NULL
               AND @vCDCEnableInd = 0
              BEGIN
                  SELECT @vSQLStatment = 'EXEC sys.sp_cdc_enable_table
      @source_schema = ''' + @pSchemaName
                                         + ''',
      @source_name   = ''' + @pTableName
                                         + ''',
      @role_name     = null;'

                  EXEC sp_executesql
                    @vSQLStatment

                  PRINT ' CDC Enabled on ::' + @pTableName
                        + ' for all the columns'
              END

            -- IF COLUMN LIST IS PROVIDED FOR CDC

            IF ( LEN(@pColumnList) > 0 )
              BEGIN
                  SET @vXML=CAST('' + REPLACE(@pColumnList, ',', '')
                                 + '
' AS XML);
                  -- CHECK IF Incorrect COLUMN NAME IS PROVIDEDED

                  SELECT @vColumnCount = COUNT(*)
                  FROM   @vXML.nodes('/a') AS R(nref)
                  WHERE  NOT EXISTS (SELECT 1
                                     FROM   INFORMATION_SCHEMA.COLUMNS I
                                     WHERE  I.TABLE_NAME = @pTableName
                                     AND I.COLUMN_NAME = nref.value('.', 'nvarchar(50)'))

                  IF ( @vColumnCount <> 0 )
                   PRINT ' The List of Columns provided do not exit in Source Table.Check the ColumnList'
                  -- IF All columns Exists in Source Table, Enable CDC

                  IF ( @vColumnCount = 0
                       AND @vCDCEnableInd = 1 )
                    BEGIN
                        PRINT ' The CDC is already Enabled for this table.'
                    END

                  IF ( @vColumnCount = 0
                       AND @vCDCEnableInd = 0 )
                    BEGIN
                        --Enable CDC

                        SET @vSQLEnableCDC='EXEC sys.sp_cdc_enable_table
         @source_schema=''' + @pSchemaName
                                           + ''',@source_name=''' + @pTableName
                                           + ''', @role_name=NULL, @captured_column_list= '''
                                           + @pColumnList + ''''

                        EXEC (@vSQLEnableCDC)

                        PRINT ' CDC Enabled on ::' + @pTableName + ' for '
                              + @pColumnList + ' Columns.'
                    END
              END
        END
  END