SQL Server 存儲過程實戰：批量操作多張表與欄位的動態 SQL 技巧

應用場景

當需要對符合特定命名規則的所有資料表、欄位執行相同操作（如統一修改資料型別、新增預設值、更新資料等）時，手寫大量重複 SQL 不僅低效且易出錯。本文介紹的預存程序 SP_execSQLonDB 可動態組裝並批量執行指定 SQL。

參數說明

核心邏輯

1. 參數清理：去除首尾空格、統一大小寫，驗證 @INCLUDE_NTI 合法性。
2. 建立游標：查詢 sysobjects、syscolumns、sysusers 系統表，取得符合條件的「資料表.擁有者」與「欄位名」清單。
   • 若 @INCLUDE_NTI = 'N'，排除 xtype 為 35、99、34（Text/NText/Image）的欄位。
   • 依 @TABLENAME、@COLUMNNAME 以 LIKE 進行模糊比對。
3. 迴圈執行：逐筆 FETCH 游標，動態組裝最終 SQL 並 EXEC sp_executesql 執行。

程式碼片段（關鍵片段）

SET @strSQL = N'DECLARE TabColCursor CURSOR FOR 
    SELECT RTRIM(LTRIM(SU.name)) + ''.'' + LTRIM(RTRIM(SO.name)), SC.name 
    FROM sysobjects SO 
    INNER JOIN syscolumns SC ON SO.id = SC.id 
    INNER JOIN sysusers SU ON SO.uid = SU.uid 
    WHERE SO.xtype = ''U'' '

IF @INCLUDE_NTI = 'N'
    SET @strSQL = @strSQL + ' AND SC.xtype NOT IN (35, 99, 34) '

IF @TABLENAME IS NOT NULL AND LTRIM(RTRIM(@TABLENAME)) <> ''
BEGIN
    SET @TABLENAME = REPLACE(@TABLENAME, ', ', ',')
    SET @strSQL = @strSQL + ' AND (SO.name LIKE ''' + REPLACE(@TABLENAME, ',', ''' OR SO.name LIKE ''') + ''')'
END
-- 類似處理 @COLUMNNAME ...

使用範例

-- 將所有以 'tbl_' 開頭的資料表中，名稱包含 'date' 的欄位型別改為 DATETIME
EXEC SP_execSQLonDB 
    @TABLENAME = 'tbl_%', 
    @COLUMNNAME = '%date%', 
    @SQL = 'ALTER TABLE ? ALTER COLUMN ? DATETIME', 
    @INCLUDE_NTI = 'N'

注意事項

• 動態 SQL 需防範注入，確保參數來源可控。
• 大量 DDL 操作可能造成鎖定，建議在維護窗口執行。
• 測試環境先行驗證，確認影響範圍。