不公開預存程序sp_Msforeachtable與sp_Msforeachdb詳解

一.簡要介紹:

系統預存程序sp_MSforeachtable和sp_MSforeachdb,是微軟提供的兩個不公開的預存程序。從mssql6.5開始，存放在SQL Server的MASTER資料庫中。可以用來對某個資料庫的所有表或某個SQL伺服器上的所有資料庫進行管理，下面將對此進行詳細介紹。

作為資料庫管理者或開發人員等經常會檢查整個資料庫或使用者表。

如:檢查整個資料庫的容量，看指定資料庫所有使用者表的容量,所有表的記錄數等等，我們一般處理這樣的問題都是通過遊標來達到要求。

如果我們用sp_MSforeachtable就可以非常方便的達到相同的目的，

如：sql查詢所有使用者表的列表，詳細資料，如：記錄數，表佔用大小等

EXEC sp_MSforeachtable "EXECUTE sp_spaceused '?'"

 

二.各參數說明:

  

  @command1 nvarchar( 2000),          --第一條啟動並執行SQL指令
  @replacechar nchar( 1) = N'?',      --指定的預留位置號
  @command2 nvarchar( 2000)= null,    --第二條啟動並執行SQL指令
  @command3 nvarchar( 2000)  = null,  --第三條啟動並執行SQL指令
  @whereand nvarchar( 2000)  = null,  --可選條件來選擇表
  @precommand nvarchar( 2000)= null,  --執行指令前的操作(類似控制項的觸發前的操作)
  @postcommand nvarchar( 2000)= null  --執行指令後的操作(類似控制項的觸發後的操作)

 以後為sp_MSforeachtable的參數，sp_MSforeachdb不包括參數@whereand

 我們在master資料庫裡執行下面的語句可以看到兩個proc詳細的代碼

use master
exec sp_helptext sp_MSforeachtable
exec sp_helptext sp_Msforeachdb

 

 

三、使用舉例:

   --統計資料庫裡每個表的詳細情況：

  exec sp_MSforeachtable @command1="sp_spaceused '?'"
 
  --獲得每個表的記錄數和容量:
  EXEC sp_MSforeachtable @command1="print '?'",
                         @command2="sp_spaceused '?'",
                         @command3= "SELECT count(*) FROM ? "
 
  --獲得所有的資料庫的儲存空間:
  EXEC sp_MSforeachdb @command1="print '?'",
                      @command2="sp_spaceused "
 
  --檢查所有的資料庫
  EXEC sp_MSforeachdb @command1="print '?'",
                      @command2="DBCC CHECKDB (?) "
 
  --更新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_MSforeachtable @command1='Delete from ?'
  sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
 
--查詢資料庫所有表的記錄總數
CREATE TABLE #temp (TableName VARCHAR ( 255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp
 
--檢查資料庫裡每個表或索引檢視表的資料、索引及text、ntext 和image 頁的完整性
--下列語句需在單一使用者模式下執行（sp_dboption 'db_name', 'single user', 'true'）
--,將true改成false就又變成多使用者了
exec sp_msforeachtable "dbcc checktable('?',repair_rebuild)"