I have multiple tables like around 2000 tables, in that few tables are change_tracking is enabled tables. I need to add extra column to those change_tracking_enabled tables only.

My code is adding columns to all 2000 tables. I need to add columns for only CHANGE_TRACKING_ENABLED tables only. Please help me

1. Here is my code to display the change_tracking_enabled tables.

   USE Test
   GO
   SELECT s.name as Schema_name, t.name AS Table_name, tr.*
   FROM sys.change_tracking_tables tr
   INNER JOIN sys.tables t on t.object_id = tr.object_id
   INNER JOIN sys.schemas s on s.schema_id = t.schema_id
   

2. Here is my code that adds columns to all 2000 tables.

   USE TestDB
   GO
   --Declare Variables
   DECLARE @TableName VARCHAR(100)
   DECLARE @TableSchema VARCHAR(100)
   DECLARE @COLUMN_NAME VARCHAR(50)
   SET @COLUMN_NAME='ddtm' -- Change Column Name according to your requirement
   DECLARE @COLUMN_DATATYPE VARCHAR(50)
   SET @COLUMN_DATATYPE='DateTime' -- Provide data type for column
   
   --Declare Cursor
   DECLARE CUR CURSOR FOR
     SELECT TABLE_SCHEMA,
            TABLE_NAME
     FROM   INFORMATION_SCHEMA.TABLES
     WHERE  TABLE_TYPE = 'BASE TABLE'
   --OPEN CURSOR
   OPEN CUR
   --Fetch First Row
   FETCH NEXT FROM CUR INTO @TableSchema,@TableName
   --Loop
   WHILE @@FETCH_STATUS = 0
     BEGIN
   
     DECLARE @SQL NVARCHAR(MAX)
     SET @SQL=NULL
     IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME=@TableName AND COLUMN_NAME=@COLUMN_NAME 
   and Table_Schema=@TableSchema)
     BEGIN
     SET @SQL='ALTER TABLE ' @TableSchema '.' @TableName  ' ADD ' @COLUMN_NAME
                              ' ' @COLUMN_DATATYPE
       PRINT @SQL
       EXEC ( @SQL)
     END
   
     IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME=@TableName AND COLUMN_NAME=@COLUMN_NAME 
   and Table_Schema=@TableSchema)
     BEGIN
     PRINT 'Column Already exists in Table'
     END
         FETCH NEXT FROM CUR INTO @TableSchema,@TableName
     END
   --Close and Deallocate Cursor
   CLOSE CUR
   DEALLOCATE CUR
   

CodePudding user response：

The queries

  SELECT TABLE_SCHEMA,
         TABLE_NAME
  FROM   INFORMATION_SCHEMA.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'

and

SELECT s.name as Schema_name, t.name AS Table_name, tr.*
FROM sys.change_tracking_tables tr
INNER JOIN sys.tables t on t.object_id = tr.object_id
INNER JOIN sys.schemas s on s.schema_id = t.schema_id

Are not the same, of course you're getting different results... Use the query you use in step 1 (minus the tr.*) for the query in step 2; as that gives you the tables that have CDC.