SQL Server 列存在性檢查與動態 DDL：判斷列是否存在、增刪改列、重命名完整腳本

動態修改表結構前，必須檢查目標列是否存在，避免重複添加報錯或誤刪。本文提供標準檢查腳本與增刪改查重命名全套模板。

檢查列是否存在

-- 方法 1: sys.columns (推薦，支援所有版本)
IF EXISTS (
    SELECT 1 FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'dbo.YourTable') AND name = N'YourColumn'
)
BEGIN
    PRINT N'列已存在';
END
ELSE
BEGIN
    PRINT N'列不存在，可安全添加';
END

-- 方法 2: INFORMATION_SCHEMA.COLUMNS (ANSI 標準)
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'YourTable' AND COLUMN_NAME = 'YourColumn'
)
BEGIN
    -- ...
END

動態 DDL 模板集

1. 添加列 (含默認值、非空、主鍵判斷)

DECLARE @TableName SYSNAME = N'YourTable';
DECLARE @ColName SYSNAME = N'NewCol';
DECLARE @ColDef NVARCHAR(MAX) = N'INT NOT NULL DEFAULT 0';  -- 完整列定義
DECLARE @Sql NVARCHAR(MAX);

-- 檢查表是否已有主鍵
IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID(@TableName) AND type = 'PK')
BEGIN
    SET @Sql = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' ADD ' + QUOTENAME(@ColName) + N' ' + @ColDef + N';';
END
ELSE
BEGIN
    -- 無主鍵時可選擇加為主鍵 (視業務)
    SET @Sql = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' ADD ' + QUOTENAME(@ColName) + N' ' + @ColDef + N' PRIMARY KEY;';
END

EXEC sp_executesql @Sql;

2. 刪除列

DECLARE @Sql NVARCHAR(MAX) = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' DROP COLUMN ' + QUOTENAME(@ColName) + N';';
EXEC sp_executesql @Sql;

3. 修改列類型/長度/空值性

-- 修改為 VARCHAR(200) NOT NULL
DECLARE @Sql NVARCHAR(MAX) = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' ALTER COLUMN ' + QUOTENAME(@ColName) + N' VARCHAR(200) NOT NULL;';
EXEC sp_executesql @Sql;

4. 重命名列/表/對象

-- 重命名列
EXEC sp_rename N'dbo.YourTable.OldCol', N'NewCol', N'COLUMN';

-- 重命名表
EXEC sp_rename N'dbo.OldTable', N'NewTable', N'OBJECT';

-- 重命名索引/約束/類型 同理，第三參數對應 OBJECT/COLUMN/INDEX/TYPE 等

完整自適應腳本：確保列存在且符合目標定義

DECLARE @TableName SYSNAME = N'Products';
DECLARE @ColName SYSNAME = N'SkuCode';
DECLARE @TargetDef NVARCHAR(MAX) = N'VARCHAR(50) NOT NULL DEFAULT ''''';  -- 目標定義

-- 1. 檢查列是否存在
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = @ColName)
BEGIN
    -- 不存在 → 添加
    DECLARE @Sql NVARCHAR(MAX) = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' ADD ' + QUOTENAME(@ColName) + N' ' + @TargetDef + N';';
    EXEC sp_executesql @Sql;
    PRINT N'已添加列 ' + @ColName;
END
ELSE
BEGIN
    -- 存在 → 檢查定義是否一致 (類型、長度、空值、默認值)
    DECLARE @CurrentDef NVARCHAR(MAX);
    SELECT @CurrentDef = 
        ty.name + 
        CASE WHEN ty.name IN ('varchar','nvarchar','varbinary','char','nchar','binary') 
             THEN N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length / CASE WHEN ty.name LIKE 'n%' THEN 2 ELSE 1 END AS NVARCHAR(10)) END + N')'
             ELSE N'' END +
        CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END +
        ISNULL(N' DEFAULT ' + dc.definition, N'')
    FROM sys.columns c
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
    LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
    WHERE c.object_id = OBJECT_ID(@TableName) AND c.name = @ColName;

    IF @CurrentDef <> @TargetDef
    BEGIN
        -- 定義不一致 → 修改 (需分步：先改類型/長度，再改空值/默認值)
        PRINT N'列定義不一致，當前: ' + @CurrentDef + N'，目標: ' + @TargetDef;
        -- 此處建議人工介入或拆分 ALTER COLUMN 步驟，避免數據截斷
    END
    ELSE
    BEGIN
        PRINT N'列定義已符合目標';
    END
END

關鍵注意事項

• ALTER COLUMN **不支援** 直接修改默認值，需先 DROP CONSTRAINT 默認約束 → ALTER COLUMN → ADD CONSTRAINT 新默認值。
• 修改列類型/長度可能導致**數據截斷/轉換失敗**，生產環境必須先在備份庫驗證。
• sp_rename 重命名列後，**存儲過程/視圖/函數/應用代碼**中引用該列的處需同步修改，工具 (SSDT/Redgate/自研) 可自動重構。
• 動態 DDL **務必用 QUOTENAME()** 包裹標識符，防注入、支援特殊字符名。