SQL Server 2005 批次查詢自訂物件原始碼：sys.sql_modules 與 OBJECT_DEFINITION() 應用

需求：一次性取得資料庫中所有檢視表、預存程序、觸發器、函數的完整建立腳本。SQL Server 2005 提供 sys.sql_modules 目錄檢視與 OBJECT_DEFINITION() 函數，可直接存取模組定義原始碼，避免舊版 syscomments 截斷 4000 字元、分行儲存的問題。

方法一：使用 OBJECT_DEFINITION() 函數

SELECT name,
       xtype,
       OBJECT_DEFINITION(id) AS definition
FROM sysobjects
WHERE xtype IN ('V', 'P', 'TR', 'IF', 'TF', 'FN')
ORDER BY xtype, name;

方法二：使用 sys.sql_modules 系統檢視 (等價且更標準)

SELECT b.name,
       b.xtype,
       a.definition
FROM sys.sql_modules a
JOIN sys.sysobjects b ON a.object_id = b.id
WHERE b.xtype IN ('V', 'P', 'TR', 'IF', 'TF', 'FN')
ORDER BY b.xtype, b.name;

物件類型代碼對照表

進階應用：產生完整重建腳本

-- 加上 DROP IF EXISTS + 建立腳本，可直接執行重建
SELECT 
    'IF OBJECT_ID(''' + SCHEMA_NAME(schema_id) + '.' + name + ''', ''' + 
    CASE xtype WHEN 'V' THEN 'V' WHEN 'P' THEN 'P' WHEN 'TR' THEN 'TR' 
               WHEN 'IF' THEN 'FN' WHEN 'TF' THEN 'FN' WHEN 'FN' THEN 'FN' END + ''') IS NOT NULL ' +
    'DROP ' + 
    CASE xtype WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' WHEN 'TR' THEN 'TRIGGER' 
               ELSE 'FUNCTION' END + ' ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + '; ' +
    CHAR(13) + CHAR(10) + 
    definition + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules a
JOIN sys.objects b ON a.object_id = b.object_id
WHERE b.type IN ('V', 'P', 'TR', 'IF', 'TF', 'FN')
ORDER BY b.type, b.name;

注意事項

• OBJECT_DEFINITION() 回傳 NVARCHAR(MAX)，不會截斷
• sys.sql_modules 只包含 SQL 模組，不含 CLR 組件、XML 結構描述等
• 加密物件 (WITH ENCRYPTION) 會回傳 NULL
• 系統物件 (is_ms_shipped = 1) 通常不需匯出，可加 AND b.is_ms_shipped = 0 過濾
• SQL Server 2000 無此功能，需用 syscomments 串接或第三方工具

匯出至檔案自動化 (PowerShell 範例)

# PowerShell + Invoke-Sqlcmd 匯出所有模組定義至 .sql 檔
$server = "localhost"
$db = "MyDatabase"
$query = @"
SELECT definition FROM sys.sql_modules a
JOIN sys.objects b ON a.object_id = b.object_id
WHERE b.type IN ('V','P','TR','IF','TF','FN') AND b.is_ms_shipped = 0
ORDER BY b.type, b.name;
"@

Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $query |
ForEach-Object { $_.definition + "; `nGO`n" } |
Set-Content -Path "C:\Backup\AllModules_$(Get-Date -Format yyyyMMdd).sql" -Encoding UTF8