SQL Server 未公開系統預存程序 sp_MSforeachtable 深度解析與應用

前言：游標與 sp_MSforeachtable 的比較

自 SQL Server 6.5 起，系統提供 sp_MSforeachtable 與 sp_MSforeachDB 兩個未文件化的系統預存程序，極大簡化對所有資料庫或使用者表的批次操作。傳統做法需撰寫游標逐一處理，例如檢查所有使用者表完整性：

傳統游標寫法

DECLARE @TableName varchar(255)
DECLARE @ExeSQL varchar(4000)

DECLARE Table_Cursor CURSOR FOR 
SELECT [name] FROM sysobjects WHERE xtype='U'

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @TableName

WHILE(@@FETCH_STATUS=0)
BEGIN
    PRINT @TableName
    SELECT @ExeSQL='DBCC CHECKTABLE('''+@TableName+''')'
    EXEC(@EXESQL)
    FETCH NEXT FROM Table_Cursor INTO @TableName
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO

使用 sp_MSforeachtable 簡化

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

單行指令即可達到相同目的，雖然底層仍使用游標處理，但程式碼簡潔許多。

sp_MSforeachtable 完整原始碼分析

執行以下指令查看原始碼：

USE MASTER
GO
SP_HELPTEXT sp_MSforeachtable

核心程式碼結構

CREATE proc sp_MSforeachtable
@command1 nvarchar(2000), 
@replacechar nchar(1) = N'?', 
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null, 
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, 
@postcommand nvarchar(2000) = null
as
/* 為每個資料表傳回一或多列 (可選擇性符合 @where)，
   每個資料表預設為自己的結果集 */
/* @precommand 和 @postcommand 可用於透過暫存表強制單一結果集 */

/* 前置處理器不會替換引號內容，故使用 str() */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
    exec(@precommand)

/* 建立選取游標 */
exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' 
    + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
    + N' where OBJECTPROPERTY(o.id, ...')

參數詳細說明

實戰應用範例

範例 1：取得所有資料表列數統計

EXEC sp_MSforeachtable 
    @precommand = 'CREATE TABLE #rowcounts (tablename sysname, rowcnt int)',
    @command1 = 'INSERT INTO #rowcounts SELECT ''?'', COUNT(*) FROM ?',
    @postcommand = 'SELECT * FROM #rowcounts ORDER BY rowcnt DESC'

範例 2：重建所有資料表索引（填充因子 90%）

EXEC sp_MSforeachtable 
    @command1 = "DBCC DBREINDEX('?', '', 90)"

範例 3：針對特定結構描述的表格操作

EXEC sp_MSforeachtable 
    @whereand = "AND schema_name(schema_id) = 'Sales'",
    @command1 = "PRINT 'Processing ?'"

重要注意事項

• 此為未文件化系統程序，微軟不保證未來版本相容性
• 底層使用全域游標 hCForEach，並發環境需謹慎
• 資料表名稱自動處理括號與特殊字元（REPLACE(object_name(id), N']', N']]')）
• 大量資料表操作時建議搭配 @precommand/@postcommand 使用暫存表聚合結果

延伸：sp_MSforeachDB 資料庫層級批次操作

同理可用 sp_MSforeachDB 對所有資料庫執行命令，參數結構類似，? 代表資料庫名稱。例如：

EXEC sp_MSforeachDB @command1="PRINT '?' DBCC CHECKDB('?')"