[翻譯]SQL Server 未公開的兩個預存程序sp_MSforeachtable 和 sp_MSforeachdb

SQL Server 未公開的兩個預存程序sp_MSforeachtable 和 sp_MSforeachdb

您是否曾經寫過代碼來處理資料庫中的所有表？處理一個 SQL Server執行個體中的所有資料庫的代碼又該如何寫？然則，您是否知道有多種方法可以解決這問題？您可以建立一個遊標cursor包含所有資料表，或者包含SQL Server執行個體的所有資料庫；或者使用非公開（undocumented）的預存程序。本文將向您闡述非公開的預存程序的工作方式，以及應用執行個體向您展示如何使用它們。非公開的預存程序比之遊標更易用。

概述

Microsoft 提供了兩個非公開化的預存程序，讓您可以迭代處理資料庫中的所有表，或者SQL Server 執行個體中的所有資料庫。第一個預存程序是"sp_MSforeachtable"，讓您可以輕易地使用代碼處理資料庫中的所有表；另一個是"sp_MSforeachdb"，處理SQL Server 執行個體中的所有資料庫。讓我們深入地瞭解這兩個預存程序。

sp_MSforeachtable

"sp_MSforeachtable"沒有在線上文檔中出現，它存在於master資料庫中，可以對給定資料庫的所有表執行單條或多條T-SQL命令，請看下面的例子。

假如，您需要建立一個暫存資料表，記錄當前資料庫擁有的表的表名、行記錄數。為了實現此功能，您需要執行這樣的命令："select '<mytable>', count(*) from <mytable>"。其中"<mytable>"替換為資料庫中的每個表名，並將結果插入到暫存資料表。下面我們用遊標與非公開的"sp_MSforeachtable"來分別實現。

使用遊標的方式：

下面是輸出結果:

下面代碼應用非公開的"sp_MSforeachtable"產生相同的結果：

下面是結果:

可見，使用遊標與sp_MSforeachtable可產生相同的結果，您認為哪種方式更具可讀性，更簡單？下面來詳細介紹sp_MSforeachtable的文法：

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,

@command3, @whereand, @precommand, @postcommand

說明:

• @RETURN_VALUE – 傳回值
  
• @command1 – 類型是nvarchar(2000)，sp_MSforeachtable最先執行的命令
  
• @replacechar – 處理過程中，將命令列的這個字元替換為具體的表名（預設是"?"）
  
• @command2\@command3:對每個資料表，都會執行這兩條命令，@command2在@command1之後執行，@command3在@command2之後執行
  
• @whereand – 類型是varchar(2000),提供額外的約束來過濾 sysobjects 表的行
  
• @precommand - 類型是varchar(2000)，在處理任何錶之前執行此命令
  
• @postcommand - 類型是varchar(2000)，在處理完所有表之後執行此命令
  

下面幾個例子示範此預存程序的用法，處理所有表或者部分表。

下面查詢以字母 p 開頭的表，使用參數 @whereand 設定過濾條件，代碼如下：

下面是結果:

上面的代碼使用了參數 @command1 與 @whereand，參數 @whereand 用來設定 WHERE 條件，篩選出以字母 p 開頭的表名，我設定了參數值為"and o.name like ''p%''"。如果您希望使用多個條件約束，如以 p 開頭或者以 a 開頭，設定參數值為：

and o.name like ''p%'' or o.name like ''a%''

 

如果語句有問題，將 name 的首碼去掉，如下：

 

and name like ''p%'' or name like ''a%''

 

注意，上面例子的參數 @command1 使用了"?"，它叫做替換字元（replacement character），預設被所有表名替換。如果您需要在命令中使用"?"作為內容而不是被表名替換的替換字元，那麼可以使用參數 @replacechar 來設定替換字元。下面例子使用"{"作為替換字元。

下面是結果:

還有兩個參數 @precommand 與 @postcommand，看下面例子，把上面例子中的所有語句整合為一個簡潔的預存程序調用。

注意上面例子用了全域暫存資料表 ##rowcount，如果用暫存資料表 #rowcount會報錯。參數 @precommand 建立全域暫存資料表，只執行了一次，並先於 @command1 的語句執行。@postcommmand 的語句待迭代處理完所有表後執行，也僅執行一次，用於顯示結果並刪除暫存資料表。

 

sp_MSforeachdb

sp_MSforeachdb 同樣也是在 master 資料庫中，它迭代SQL Server 執行個體中的每個資料庫，以執行T-SQL 陳述式，如"DBCCCHECKDB"，在看看它的文法

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,

@command2, @command3, @precommand, @postcommand

說明:

• @RETURN_VALUE – 傳回值
  
• @command1 – 類型是 nvarchar(2000),最先執行的命令
  
• @replacechar – 替換字元，命令字串中被替換為實際的資料庫名（預設是"?"）
  
• @command2\@command3:對每個資料庫，都會執行這兩條命令，@command2在@command1之後執行，@command3在@command2之後執行
  
• @precommand - 類型是varchar(2000)，在處理任何資料庫之前執行此命令
  
• @postcommand - 類型是varchar(2000)，在處理完所有資料庫之後執行此命令
  

sp_MSforeachdb 的參數與sp_MSforeachtable 的參數類似，因此，不再特意介紹這些參數。

請看下面的簡單例子，此例子將進行Database Backup，然後對每個資料庫做"DBCC CHECKDB"：

這裡我用了三條不同的命令，第一條列印正在處理的資料庫名。sp_MSforeachtable 有一個參數用來過濾需要處理的資料表，但是sp_MSforeachdb沒有類似的過濾參數。由於SQL Server 不支援對 tempdb 的備份，因此我要跳過tempdb，這是我在每條命令使用 IF 的原因。第二條命令進行Database Backup，最後一條命令對除 tempdb 之外的資料庫運行"DBCC CHECKDB"。

運行上面命令之前要先建立目錄"c:\temp",下面是部分輸出結果：

 

使用SQL Server非公開預存程序的說明

當使用這些非公開的預存程序時您須小心，並進行測試。由於未公開，意味著Microsoft在任何版本的升級或者補丁包都可能對它們進行修改，並且不做任何告知。因此，您需要在所有的SQL Server版本做全面的測試，測試以驗證您的代碼是否在新版本中仍然正常運行。

結語

正如您所見，這兩個非公開的預存程序比遊標易用，以後您可以用它們來迭代處理資料表或資料庫。但是請謹記，這兩個預存程序是非公開的，Microsoft很可能會隨時改變它們的功能。

參考

SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

sp_MSforeachtable