SQL Server 在指定位置新增欄位：修改系統表 colid 實作預存程序

背景

ALTER TABLE ... ADD 只能將新欄位加在最後。若需插入到中間位置，需調整系統表 syscolumns.colid。

預存程序：addcolumn

CREATE PROC addcolumn
    @tablename VARCHAR(30),   -- 表名
    @colname   VARCHAR(30),   -- 新欄位名
    @coltype   VARCHAR(100),  -- 新欄位型別
    @colid     INT            -- 目標位置 (1-based)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @colid_max INT, @sql VARCHAR(1000);

    -- 1. 檢查表存在
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = @tablename AND xtype = 'U')
    BEGIN RAISERROR(20001, '表不存在', 1); RETURN -1; END

    -- 2. 檢查欄位不重複
    IF EXISTS (SELECT 1 FROM syscolumns WHERE id = OBJECT_ID(@tablename) AND name = @colname)
    BEGIN RAISERROR(20002, '欄位已存在', 1); RETURN -1; END

    -- 3. 取得目前最大 colid
    SELECT @colid_max = MAX(colid) FROM syscolumns WHERE id = OBJECT_ID(@tablename);
    IF @colid > @colid_max OR @colid < 1 SET @colid = @colid_max + 1;

    -- 4. 先加在最後
    SET @sql = 'ALTER TABLE ' + @tablename + ' ADD ' + @colname + ' ' + @coltype;
    EXEC(@sql);

    -- 5. 取得新欄位 colid
    SELECT @colid_max = colid FROM syscolumns WHERE id = OBJECT_ID(@tablename) AND name = @colname;
    IF @@ROWCOUNT <> 1 BEGIN RAISERROR(20003, '新增欄位失敗', 1); RETURN -1; END

    -- 6. 開啟系統表更新
    EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;

    -- 7. 新欄位暫設 -1
    SET @sql = 'UPDATE syscolumns SET colid = -1 WHERE id = OBJECT_ID('''+@tablename+''') AND colid = '+CAST(@colid_max AS VARCHAR(10));
    EXEC(@sql);

    -- 8. 目標位置之後的欄位 +1
    SET @sql = 'UPDATE syscolumns SET colid = colid + 1 WHERE id = OBJECT_ID('''+@tablename+''') AND colid >= '+CAST(@colid AS VARCHAR(10));
    EXEC(@sql);

    -- 9. 新欄位歸位
    SET @sql = 'UPDATE syscolumns SET colid = '+CAST(@colid AS VARCHAR(10))+' WHERE id = OBJECT_ID('''+@tablename+''') AND name = '''+@colname+'''';
    EXEC(@sql);

    -- 10. 關閉系統表更新
    EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;
END;
GO

呼叫範例

EXEC addcolumn 'test', 'id2', 'CHAR(10)', 2;  -- 將 id2 插入第 2 個位置

⚠️ 風險與限制

• 需 sysadmin 權限 才能執行 sp_configure 'allow updates'。
• 直接修改系統表不受 Microsoft 支援，可能導致資料庫損壞、升級失敗。
• SQL Server 2005+ 已移除 allow updates 選項，此法僅適用 SQL 2000。
• 現代版本建議：建立新表 → 複製資料 → 重命名，或使用視圖調整欄位順序。

現代替代方案

-- 1. 建立新表 (欄位順序正確)
CREATE TABLE dbo.test_new (id INT, id2 CHAR(10), col3 ...);
-- 2. 複製資料
INSERT INTO dbo.test_new (id, col3, ...) SELECT id, col3, ... FROM dbo.test;
-- 3. 切換
BEGIN TRANSACTION;
  DROP TABLE dbo.test;
  EXEC sp_rename 'dbo.test_new', 'test';
COMMIT;

雖然修改 colid 技術可行，但強烈不建議在生產環境使用，請採用重建表或視圖方案。