Last week a colleague texted, wondering how to ALTER a COLUMN NAME because they were getting the error message: “Cannot alter column because it is ‘REPLICATED’.
The issue was that Change Data Capture (CDC) was enabled on the table. Before I can change the column name, I must first disable the CDC on the table. Here are the commands I used to rename the column under CDC control.
-- STEP ONE: DROP CDC from table.
exec sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name=N'MyTableName', @capture_instance='dbo_MyTableName';
-- STEP TWO: Rename the column.
-- Notice I am removing a space -->>|
EXEC sp_rename 'dbo.MyTableName.JobId ', 'JobId';
-- STEP THREE: Reenable CDC on the table.
exec sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name=N'MyTableName', @role_name=N'db_cdcadmin', @capture_instance='dbo_MyTableName, @supports_net_changes=1, @index_name='PK_MyTableName_JobId', @captured_column_list='JobId,BatchId,DateSysCreated,OtherColumn';I can verify my CDC information by scanning through a list of tables monitored using this code. This list may also be useful before STEP ONE, in helping get the properties and current configuration for the table.
--View CDC configuration
use MyDatabase;
exec sys.sp_cdc_help_change_data_capture;Cheers!