將列添加到特定表-有解無憂

我有多個表，比如大約 2000 個表，因為很少有表啟用了 change_tracking 表。我只需要向那些 change_tracking_enabled 表添加額外的列。

我的代碼正在向所有 2000 個表添加列。我只需要為 CHANGE_TRACKING_ENABLED 表添加列。請幫我

這是我顯示 change_tracking_enabled 表的代碼。

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

這是我將列添加到所有 2000 個表的代碼。

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

uj5u.com熱心網友回復：

查詢

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

和

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

是不一樣的，當然你得到不同的結果...使用您在步驟1（減去使用查詢tr.*在步驟2中的查詢）; 因為這為您提供了具有 CDC 的表。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379329.html

標籤：sql sql-server 查询语句

上一篇：SQL我可以在JOIN條件中使用帶有附加條件的CASE陳述句嗎

下一篇：當計數大于0時，SQL回傳1以防切換