How to Disable CDC On Set Of Tables OR Disable On All Tables In a Database in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or Developer, You need to prepare script that should be able to Disable Change Data Capture (CDC) on all the tables in a database Or if you would like to disable on some specific table , it should be able to handle that.

The below script can be used to Disable Change Data Capture on all the tables in a database and if you would like to provide the list of tables, you have the option too. By simply changing select query, you can include or exclude list of tables on which you would like to disable CDC.

/*------------------------------------------------
Disable CDC on Set of Tables
--------------------------------------------------*/

DECLARE @TableName VARCHAR(100)

DECLARE CDC_Cursor CURSOR FOR

--Provide List of Tables here on which CDC needs to be disabled. 
  SELECT *
  FROM   (SELECT 'T' AS TableName
          UNION ALL
          SELECT 'T2' AS TableName
         --IF want to Disable CDC on All Table, then use
         --SELECT Name
         --FROM   sys.objects
         --WHERE  type = 'u'
         --       AND is_ms_shipped <> 1
         ) CDC
OPEN CDC_Cursor
FETCH NEXT FROM CDC_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(1000)
      DECLARE @CDC_Status TINYINT

      SET @CDC_Status=(SELECT COUNT(*)
                       FROM   cdc.change_tables
                       WHERE  Source_object_id = OBJECT_ID(@TableName))

      --IF CDC is Already Disabled on Table , Print Message
      IF @CDC_Status = 0
        PRINT 'CDC is already Disabled on ' + @TableName
              + ' Table'

      --IF CDC is not Disabled on Table, Disable CDC and Print Message
      IF @CDC_Status = 1
        BEGIN
            SET @SQL='EXEC sys.sp_cdc_disable_table
      @source_schema = ''dbo'',
      @source_name   = ''' + @TableName
                     + ''',
      @capture_instance     = N''All'';'

            EXEC sp_executesql
              @SQL

            PRINT 'CDC  disabled on ' + @TableName
                  + ' Table successfully'
        END

      FETCH NEXT FROM CDC_Cursor INTO @TableName
  END

CLOSE CDC_Cursor
DEALLOCATE CDC_Cursor