SQL Server 新舊資料庫資料字典比對腳本：升級遷移前的必備工具

系統升級改造前，需精確比對新舊資料庫結構差異 (新增/刪除資料表、欄位、索引、預設值、主鍵、擴展屬性說明)。本腳本假設新舊庫在同一實例，透過暫存表 #old、#new 擷取完整字典，再依參數 @i 輸出四種差異報表，直接支援遷移腳本產生。

核心邏輯

1. 切換至舊庫 (USE temp)，查詢 sysobjects/syscolumns/systypes/sysproperties/syscomments 組合完整字典存入 #old。
2. 切換至新庫 (USE accdb)，同邏輯存入 #new。
3. 依 @i 參數輸出：
   • 1：新庫新增的欄位級字典 (表+欄位在舊庫不存在)
   • 2：舊庫獨有的欄位級字典
   • 3：新庫新增的整張資料表字典
   • 4：舊庫獨有的整張資料表字典
4. 清理暫存表。

完整腳本 (已整理格式)

DECLARE @i INT = 4;  -- 1/2/3/4 切換輸出模式

-- 1. 擷取舊庫字典
USE temp;  -- 舊庫名
SELECT o.name AS [table],
       ISNULL(CAST(p1.[value] AS VARCHAR), '') AS 表說明,
       c.name AS field,
       ISNULL(CAST(p2.[value] AS VARCHAR), '') AS 字段說明,
       t.name AS type, c.length,
       ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS 小數位數,
       c.isnullable AS isnull,
       ISNULL(sc.text, '') AS [Default],
       CASE WHEN COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
       CASE WHEN EXISTS (
         SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN (
           SELECT name FROM sysindexes WHERE indid IN (
             SELECT indid FROM sysindexkeys WHERE id=c.id AND colid=c.colid
           )
         )
       ) THEN '√' ELSE '' END AS 主鍵
INTO #old
FROM syscolumns c
JOIN sysobjects o ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
LEFT JOIN sysproperties p1 ON o.id = p1.id AND p1.smallid = 0
LEFT JOIN sysproperties p2 ON c.id = p2.id AND c.colid = p2.smallid
LEFT JOIN syscomments sc ON c.cdefault = sc.id
WHERE o.xtype = 'U';

-- 2. 擷取新庫字典
USE accdb;  -- 新庫名
SELECT o.name AS [table],
       ISNULL(CAST(p1.[value] AS VARCHAR), '') AS 表說明,
       c.name AS field,
       ISNULL(CAST(p2.[value] AS VARCHAR), '') AS 字段說明,
       t.name AS type, c.length,
       ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS 小數位數,
       c.isnullable AS isnull,
       ISNULL(sc.text, '') AS [Default],
       CASE WHEN COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
       CASE WHEN EXISTS (
         SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN (
           SELECT name FROM sysindexes WHERE indid IN (
             SELECT indid FROM sysindexkeys WHERE id=c.id AND colid=c.colid
           )
         )
       ) THEN '√' ELSE '' END AS 主鍵
INTO #new
FROM syscolumns c
JOIN sysobjects o ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
LEFT JOIN sysproperties p1 ON o.id = p1.id AND p1.smallid = 0
LEFT JOIN sysproperties p2 ON c.id = p2.id AND c.colid = p2.smallid
LEFT JOIN syscomments sc ON c.cdefault = sc.id
WHERE o.xtype = 'U';

-- 3. 差異輸出
IF @i = 1 SELECT n.* FROM #new n LEFT JOIN #old o ON n.[table]=o.[table] AND n.field=o.field WHERE o.[table] IS NULL OR o.field IS NULL ORDER BY n.[table], n.field;
ELSE IF @i = 2 SELECT o.* FROM #new n RIGHT JOIN #old o ON n.[table]=o.[table] AND n.field=o.field WHERE n.[table] IS NULL OR n.field IS NULL ORDER BY o.[table], o.field;
ELSE IF @i = 3 SELECT * FROM #new WHERE [table] <> ALL (SELECT [table] FROM #old) ORDER BY [table], field;
ELSE IF @i = 4 SELECT * FROM #old WHERE [table] <> ALL (SELECT [table] FROM #new) ORDER BY [table], field;
ELSE PRINT '參數錯誤';

DROP TABLE #old; DROP TABLE #new;

應用場景

• 升級前跑 @i=3 確認新增表，@i=4 確認廢棄表。
• 跑 @i=1 產生 ALTER TABLE ADD COLUMN 腳本，@i=2 產生 DROP COLUMN (謹慎)。
• 比對擴展屬性說明，確保文檔同步。
• 可擴充比對索引、檢查約束、觸發器等物件。