SQL Server 刪除資料表欄位及其相關約束的預存程序腳本

背景與問題

直接執行 ALTER TABLE ... DROP COLUMN 時，若該欄位存在 **預設值約束、外鍵參考、索引/主鍵** 等相依物件，會報錯失敗。必須先移除所有相依約束，才能成功刪除欄位。

完整預存程序碼

CREATE PROCEDURE [dbo].[spDeleteColumnConstraint]
    @tablename  NVARCHAR(50),
    @columnname NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 宣告游標，收集所有需移除的約束/索引 DDL
    DECLARE tb CURSOR LOCAL FOR
        -- 1.1 預設值約束
        SELECT 'ALTER TABLE [' + b.name + '] DROP CONSTRAINT [' + d.name + ']'
        FROM syscolumns a
        JOIN sysobjects b ON a.id = b.id
        JOIN syscomments c ON a.cdefault = c.id
        JOIN sysobjects d ON c.id = d.id
        WHERE b.name = @tablename AND a.name = @columnname

        UNION ALL
        -- 1.2 外鍵參考 (其他表參考此欄位)
        SELECT 'ALTER TABLE [' + c.name + '] DROP CONSTRAINT [' + b.name + ']'
        FROM sysforeignkeys a
        JOIN sysobjects b ON b.id = a.constid
        JOIN sysobjects c ON c.id = a.fkeyid
        JOIN syscolumns d ON d.id = c.id AND a.fkey = d.colid
        JOIN sysobjects e ON e.id = a.rkeyid
        JOIN syscolumns f ON f.id = e.id AND a.rkey = f.colid
        WHERE e.name = @tablename AND d.name = @columnname

        UNION ALL
        -- 1.3 索引 / 主鍵 / 唯一約束
        SELECT CASE e.xtype 
                   WHEN 'PK' THEN 'ALTER TABLE [' + c.name + '] DROP CONSTRAINT [' + e.name + ']'
                   ELSE 'DROP INDEX [' + c.name + '].[' + a.name + ']'
               END
        FROM sysindexes a
        JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id AND c.xtype = 'U' AND c.name <> 'dtproperties'
        JOIN syscolumns d ON b.id = d.id AND b.colid = d.colid
        JOIN sysobjects e ON c.id = e.parent_obj
        WHERE a.indid NOT IN (0, 255)
          AND c.name = @tablename
          AND d.name = @columnname;

    -- 2. 執行收集到的 DDL
    DECLARE @s NVARCHAR(MAX);
    OPEN tb;
    FETCH NEXT FROM tb INTO @s;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_executesql @s;
        FETCH NEXT FROM tb INTO @s;
    END
    CLOSE tb;
    DEALLOCATE tb;

    -- 3. 最後刪除欄位
    DECLARE @SQL NVARCHAR(500);
    SET @SQL = 'ALTER TABLE ' + QUOTENAME(@tablename) + ' DROP COLUMN ' + QUOTENAME(@columnname);
    EXEC sp_executesql @SQL;
END;
GO

使用範例

EXEC spDeleteColumnConstraint 'Orders', 'Discount';

關鍵點解析

1. 系統表查詢：使用 SQL 2000 相容系統表 (syscolumns, sysobjects, syscomments, sysforeignkeys, sysindexes, sysindexkeys)；SQL 2005+ 建議改用 sys.columns, sys.objects, sys.foreign_keys, sys.indexes 等目錄檢視。
2. 動態 SQL：約束名稱不固定，需動態組裝並執行。
3. 游標逐筆執行：確保每個 DDL 獨立執行，單一失敗不影響後續 (可加 TRY/CATCH 記錄錯誤)。
4. QUOTENAME：防止物件名稱含特殊字元/保留字導致語法錯誤。

注意事項與擴充建議

• 權限：執行者需具備 ALTER 資料表權限與 CONTROL 約束權限。
• 外鍵方向：腳本處理「其他表參考此欄位」的情況；若「此表參考其他表」需另行處理。
• 叢集索引：欄位為叢集索引鍵值時，需先移除叢集索引 (隱含主鍵/唯一約束)。
• 擴充：可加入 @PrintOnly BIT 參數，僅列印 DDL 不執行，供審核部署。

適用版本

SQL Server 2000 / 2005 / 2008 / 2012 / 2014 / 2016 / 2017 / 2019 (系統表查詢部分需依版本微調)。