quote:

---

Originally posted by NguyenL71

I need to capture only certain columns to enable CDC and it must use dynamic sequel because they want to enable different databases
and tables without a lot of changes.  How can I capture only columns below to put into the Capture_Column_list.
SQL 2012

Thanks so much in advance.

Declare @Capture_Col_List VARCHAR(8000) = '',
        @cap_col_list VARCHAR(8000),
        @sqlCommand VARCHAR(MAX),
        @DBname  VARCHAR(50) = 'Dev',
		@SchemaName VARCHAR(50) = 'dbo',
	    @TableName  VARCHAR(50) = 'MyPlan_' --'dbo_TaskDocument'

SET @Capture_Col_List = '
  SELECT SUBSTRING( ( SELECT '', '' + COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
					   WHERE Table_Name = ''' + @TableName + '''
					     AND COLUMN_NAME NOT IN ( ''Benefits'', ''BenefitsRaw'')
					     FOR XML PATH( '''') ), 2, 8000);'
--PRINT @Capture_Col_List

SET @cap_col_list = @Capture_Col_List
EXECute (@Capture_Col_List)

--  Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal

Result wants:
-------------
@cap_col_list = 'Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal'

SET @sqlCommand = '
EXEC sys.sp_cdc_enable_table
                   ,@source_schema = ''' + @SchemaName + '''
                   ,@source_name   =  ''' + @TableName + '''
				   ,@role_name     = Null
                   ,@supports_net_changes = 0 --cdc.fn_cdc_get_net_changes_<capture_instance> is not created
				   ,@capture_column_list = ''' + @cap_col_list + '''

'
print @sqlCommand
EXECute (@sqlCommand)

---