淺析SQL Server未公開系統存儲過程：sp_MSforeachtable與sp_MSforeachDB

背景

DBA常需對所有表/資料庫執行同類操作（如DBCC CHECKTABLE、更新統計、空間統計）。傳統寫法需游標遍歷sysobjects，SQL Server 6.5+內建未文檔化的sp_MSforeachtable、sp_MSforeachDB簡化此類批次作業（內部仍用游標）。

sp_MSforeachtable 參數詳解

EXEC sp_MSforeachtable
  @command1 NVARCHAR(2000),      -- 第一條T-SQL，必填
  @replacechar NCHAR(1) = N'?',  -- 佔位符，預設?
  @command2 NVARCHAR(2000) = NULL,
  @command3 NVARCHAR(2000) = NULL,
  @whereand NVARCHAR(2000) = NULL,  -- 額外WHERE條件篩選表
  @precommand NVARCHAR(2000) = NULL, -- 迴圈前執行
  @postcommand NVARCHAR(2000) = NULL -- 迴圈後執行

實戰範例

1. 所有表執行DBCC CHECKTABLE

EXEC sp_MSforeachtable @command1 = "PRINT '?' DBCC CHECKTABLE('?')"

2. 取得每表筆數與空間

EXEC sp_MSforeachtable
  @command1 = "PRINT '?'",
  @command2 = "sp_spaceused '?'",
  @command3 = "SELECT COUNT(*) FROM ?"

3. 更新pubs資料庫以t開頭所有表的統計

EXEC sp_MSforeachtable
  @whereand = "AND name LIKE 't%'",
  @replacechar = '*',
  @precommand = "PRINT 'Updating Statistics.....' PRINT ''",
  @command1 = "PRINT '*' UPDATE STATISTICS *",
  @postcommand = "PRINT '' PRINT 'Complete Update Statistics!'"

sp_MSforeachDB 同理

參數相同（無@whereand），針對所有資料庫：

-- 所有資料庫空間
EXEC sp_MSforeachdb @command1 = "PRINT '?'", @command2 = "sp_spaceused '?'"

-- 檢查所有資料庫
EXEC sp_MSforeachdb @command1 = "PRINT '?'", @command2 = "DBCC CHECKDB('?')"

自建通用版：sp_MSforeachObject

支援@objectType（1=Table, 2=View, 3=Trigger, 4=Procedure, 5=Default, 6=ForeignKey, 7=Scalar Function, 8=Inline Function, 9=PrimaryKey, 10=Extended Proc, 11=Repl Proc, 12=Rule），一次處理所有物件類型。

應用範例

-- 取得所有預存程序腳本
EXEC sp_MSforeachObject @command1 = "sp_helptext '?'", @objectType = 4

-- 取得所有檢視腳本
EXEC sp_MSforeachObject @command1 = "sp_helptext '?'", @objectType = 2

-- 批次將所有表/檢視/觸發器/程序Owner改為dbo
EXEC sp_MSforeachObject @command1 = "sp_changeobjectowner '?', 'dbo'", @objectType = 1
EXEC sp_MSforeachObject @command1 = "sp_changeobjectowner '?', 'dbo'", @objectType = 2
EXEC sp_MSforeachObject @command1 = "sp_changeobjectowner '?', 'dbo'", @objectType = 3
EXEC sp_MSforeachObject @command1 = "sp_changeobjectowner '?', 'dbo'", @objectType = 4

注意：非文檔化程序，版本間可能變動，生產環境謹慎使用，建議封裝自有版本。