K'隨手記

sp_MsForEachTable使用說明

sp_MsForEachTable這指令是微軟沒有記錄在公開文件的預儲程序，在Master資料庫中的系統預儲程序中可以找到這個指令，透過這指令可以輕鬆取得每一資料表的詳細資訊，也可透過這個指令執行資料表的維護作業。

於目前常見SQL Server 版本中，透過下列指令可以取得使用者自訂資料表的相關訊息。

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_spaceused 'Production.ProductProductPhoto'

GO

如果需要列出資料庫中所有資料表的訊息，就必須於上列指令一一指定每個資料表，而無法在單次查詢中就取得所有訊息，透過sp_MsForEachTable指令可以幫助你將每個資料庫中的資料表的訊息彙總起來。

執行sp_MsForEachTable必須提供參數，若未提供錯誤訊息如下所示：

在SQL Server 2000中執行sp_MsForEachTable未提供參數的錯誤訊息

在SQL Server 2008R2中執行sp_MsForEachTable未提供參數的錯誤訊息

接著借由sp_MsForEachTable指令幫助我們執行指令sp_spaceused，

完整指令如下：

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MsForEachTable "Sp_SpaceUsed '?'"

GO

--或

EXEC sp_MsForEachTable "Sp_SpaceUsed [?]"

GO

--或

EXEC sp_MsForEachTable 'EXEC Sp_SpaceUsed [?]'

GO

接著我們再來列出單一資料庫內所有資料表資料列筆數

-- SQL2000、SQL2005、SQL2008、SQL2008R2適用

USE AdventureWorks

GO

EXEC sp_MsForEachTable "SELECT '?' AS 'TableName',COUNT(*) AS 'RowCount' FROM ?"

GO

更進階的應用是我們可以把SP_SPACEUSED產生的資料彙總儲存到一個資料表，

範例如下:

USE AdventureWorks

GO

CREATE TABLE #MyTblInfo

(

[name] nvarchar(256),--資料表名稱

[rows] int,--現有資料列數量

[reserved] varchar(18),--資料表磁碟保留空間大小

[reserved_int] int default(0),--資料表磁碟保留空間大小整數值

[data] varchar(18),--資料表實體資料使用磁碟空間大小

[data_int] int default(0),--資料表實體資料使用磁碟空間大小整數值

[index_size] varchar(18),--資料表索引使用磁碟空間大小

[index_size_int] int default(0),--資料表索引使用磁碟空間大小整數值

[unused] varchar(18),--保留給資料表未使用的磁碟空間大小

[unused_int] int default(0)--保留給資料表未使用的磁碟空間大小整數值

)

GO

--將產生的資料寫入暫存資料表#MyTblInfo

EXEC sp_MSforeachtable "INSERT INTO #MyTblInfo ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused '?'"

GO

--從[reserved]、[data]、[index_size]、[unused]取出數字並更新到對應的整數值欄位

UPDATE #MyTblInfo

SET [reserved_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS int),

[data_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS int),

[index_size_int] = CAST(SUBSTRING([index_size], 1, CHARINDEX(' ', [index_size])) AS int),

[unused_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS int)

GO

--顯示資料

SELECT [name],[rows],[reserved],[reserved_int],

[data],[data_int],[index_size],[index_size_int],

[unused],[unused_int],

CAST(([reserved_int] + [data_int] + [index_size_int] + [unused_int]) /1024.0 AS VARCHAR) + ' MB' AS 'TABLESIZE',

([reserved_int] + [data_int] + [index_size_int] + [unused_int]) /1024.0 AS 'TABLESIZE_FLOAT'

FROM #MyTblInfo

ORDER BY data_int DESC

GO

--//TABLE SIZE

SELECT (CAST(SUM(data_int)/1024.0 AS VARCHAR) + ' MB') AS 'DATA_FILE_SIZE',

(CAST(SUM(index_size_int)/1024.0 AS VARCHAR) + ' MB') AS 'INDEX_FILE_SIZE',

(CAST(SUM(unused_int)/1024.0 AS VARCHAR) + ' MB') AS 'UNUSED_FILE_SIZE',

(CAST((SUM(data_int) + SUM(index_size_int) + SUM(unused_int))/1024.0 AS VARCHAR) + ' MB')AS 'TOTAL_FILE_SIZE'

FROM #MyTblInfo

GO

DROP TABLE #MyTblInfo

GO

參考資料:

SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

By Gregory A. Larsen

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm