您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

sp_MSforeachtable 與 sp_MSforeachdb：未公開系統預存程序實戰

編輯：關於SqlServer

SQL Server 內建兩個未文件化的系統預存程序 sp_MSforeachtable (遍歷單一資料庫所有資料表) 與 sp_MSforeachdb (遍歷執行個體所有資料庫)，極適合批次維護作業。

參數說明 (sp_MSforeachtable)

參數	型別	預設	用途
`@command1`	nvarchar(2000)	必填	對每個資料表執行的第 1 條 T-SQL，`?` 代表資料表名稱
`@replacechar`	nchar(1)	N'?'	替換字元，可自訂
`@command2`	nvarchar(2000)	NULL	第 2 條 T-SQL
`@command3`	nvarchar(2000)	NULL	第 3 條 T-SQL
`@whereand`	nvarchar(2000)	NULL	篩選資料表的額外 WHERE 條件
`@precommand`	nvarchar(2000)	NULL	迴圈前執行一次
`@postcommand`	nvarchar(2000)	NULL	迴圈後執行一次

sp_MSforeachdb 參數相同，僅無 @whereand，? 代表資料庫名稱。

經典用法範例

1. 統計每個資料表空間使用量

EXEC sp_MSforeachtable @command1 = "EXEC sp_spaceused '?'";

2. 查詢所有使用者資料表列數

CREATE TABLE #t (tbl sysname, cnt int);
EXEC sp_MSforeachtable 'INSERT #t SELECT ''?'', COUNT(*) FROM ?';
SELECT * FROM #t ORDER BY tbl;
DROP TABLE #t;

3. 單一資料庫模式下檢查所有資料表完整性

-- 需先設定單一使用者模式
EXEC sp_dboption 'db_name', 'single user', 'true';
EXEC sp_MSforeachtable "DBCC CHECKTABLE('?', REPAIR_REBUILD)";
EXEC sp_dboption 'db_name', 'single user', 'false';

4. 批次備份所有使用者資料庫

EXEC sp_MSforeachdb
  @precommand = "DECLARE @path nvarchar(200) = 'D:\\Backup\\'",
  @command1 = "IF '?' NOT IN ('master','model','msdb','tempdb')
    BACKUP DATABASE [?] TO DISK = @path + '?' + '.bak'";