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

於目前常見SQL Server 版本中,透過下列指令可以取得資料庫檔案的相關訊息。 

--sysfiles表格說明

--在SQL Server 2000中,sysfiles位於每一桶資料庫的系統資料表格中

--在SQL Server 2005中,sysfiles位於每一桶資料庫的檢視表中的系統檢視表

--在SQL Server 2008中,sysfiles位於每一桶資料庫的檢視表中的系統檢視表

--欄位資料型態說明

--[fileid] [smallint] NOT NULL,

--[groupid] [smallint] NOT NULL,

--[size] [int] NOT NULL,

--[maxsize] [int] NOT NULL,

--[growth] [int] NOT NULL,

--[status] [int] NOT NULL,

--[perf] [int] NOT NULL,

--[name] [nchar] (128) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,

--[filename] [nchar] (260) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL

--[name]和[filename]依每台資料庫伺服器上定序不同而有所不同

--在此範例是設定為Chinese_Taiwan_Stroke_CI_AS

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

USE 資料庫名稱

GO

SELECT * FROM sysfiles

GO

北風範例資料庫檔案資料訊息

列出資料庫實體檔案資料-V2000.png

SQL Server 2005中AdventureWorks範例資料庫檔案資料訊息

列出資料庫實體檔案資料-V2005.png

SQL Server 2008R2中AdventureWorks範例資料庫檔案資料訊息

列出資料庫實體檔案資料-V2008R2.png

如果需要列出資料庫伺服器中所有資料庫的檔案資料訊息,就必須在每個資料庫執行上列指令,而無法在單次查詢中就取得所有訊息,透過sp_MSforeachdb指令可以幫助你將每個資料的訊息彙總起來。

執行sp_MSforeachdb必須提供參數,若未提供錯誤訊息如下所示:

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

未指定參數時的錯誤訊息-v2000.png

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

未指定參數時的錯誤訊息-v2005.png

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

未指定參數時的錯誤訊息-v2008R2.png

接著藉由sp_Msforeachdb指令幫助我們執行指令SELECT * FROM sysfiles,完整指令如下:

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXECsp_Msforeachdb "USE [?];SELECT * FROM sysfiles;"

直接顯示彙總資料庫檔案-v2008R2執行結果

直接顯示彙總資料庫檔案-v2008R2.png

原本我們透過

USE資料庫名稱

GO

SELECT * FROM sysfiles

GO

sp_Msforeachdb自動幫我們將個資料庫名稱取代完整指令中的問號,以迴圈的方式列出資料庫伺服器中目前INSTANCE的所有資料庫檔案詳細資料。

EXEC sp_Msforeachdb "USE [?];SELECT * FROM sysfiles;"

上列問號可寫成[?]或是?也可以執行,所以在上列指令問號就代表的是資料庫名稱,而? 除了可當作字串外也可當作欄位或物件來查詢,例子如下:

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'SELECT ''?'' AS ''DATABASENAME'''

GO

上例會列出此資料庫伺服器單一INSTANCE中的所有資料庫名稱。

顯示所有資料庫名稱-V2008r2.png

執行下列語法可以將資料庫伺服器單一INSTANCE中的所有資料庫檔案訊息彙總到一個暫存資料表。

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

CREATE TABLE #DBInfo (

  fileid smallint,

  groupid smallint,

  size bigint,

  maxsize bigint,

  growth float,

  status int,

  perf int,

  name sysname,

  filename sysname

);

EXEC sp_Msforeachdb "USE [?];INSERT #DBInfo SELECT * FROM sysfiles;"

GO

SELECT * FROM #DBInfo

GO

DROP TABLE #DBInfo

GO

彙總資料庫檔案到暫存資料表-V2008R2

彙總資料庫檔案到暫存資料表-v2008R2.png   

如有必要可以將上列的暫存資料表改為實體資料表,可用來設計自己的資料庫監控系統,用來觀察與紀錄資料庫的使用狀況。


以下列出sp_Msforeachdb常見的應用

1.列出資料庫實體名稱

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE [?];EXEC sp_helpfile;'

GO

sp_helpfile-V2008r2.png

 

2.顯示資料庫使用的磁碟空間大小

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE [?];EXEC sp_spaceused;'

GO

sp_spaceused-V2008r2.png

  

3.將資料庫的Page_VERIFT選項設定為CHECKSUM

-- SQL2005 Best Practice

--SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'ALTER DATABASE ? SET PAGE_VERIFY CHECKSUM;'

GO

 

4. 顯示實體資料檔和記錄檔磁碟空間使用狀況

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb

'USE ?;SELECT @@servername AS ''伺服器名稱'',DB_NAME() AS ''資料庫名稱'',[FileID] AS ''檔案代碼'',

[檔案大小(MB)] = CONVERT(DECIMAL(12,2),ROUND([size]/128.000,2)),

[空間使用大小(MB)] = CONVERT(DECIMAL(12,2),ROUND(fileproperty([name],''SpaceUsed'')/128.000,2)),

[剩餘空間大小MB] = CONVERT(DECIMAL(12,2),ROUND(([size]-fileproperty([name],''SpaceUsed''))/128.000,2)),

[Name], [FileName],CONVERT(DATETIME,GetDate(),112) AS ''資料查詢時間''

FROM dbo.sysfiles;'

GO

顯示實體資料檔和記錄檔磁碟空間使用狀況-V2008r2.png

 

5.顯示資料庫訊息

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb'USE ?; EXEC sp_helpdb ?;'

GO

sp_helpdb-V2008R2.png

 

6.列出所有資料庫名稱

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE ?;PRINT DB_NAME();'

GO

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'SELECT ''?'' AS ''DATABASENAME'''

GO

 

7.顯示每一桶資料庫所有資料表名稱

--SQL2000適用

EXEC sp_MSforeachdb 'USE ?;SELECT @@SERVERNAME AS ''ServerName'',

                           DB_NAME() AS ''DbName'' ,

                           name AS ''TableName''

                           FROM sysobjects

                           WHERE (xtype = ''U'')'

GO

列出資料庫內所有表格名稱-V2000.png

 

--SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE ?;SELECT @@SERVERNAME + ''.'' +

                                  DB_NAME() + ''.'' +

                                  SCHEMA_NAME(uid) + ''.'' +                                 

                                  name AS ''TableName''

                           FROM sysobjects

                           WHERE (xtype = ''U'')'

GO

--SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE ?;SELECT @@SERVERNAME + ''.'' +

                                  Table_CATALOG + ''.'' +

                                  TABLE_SCHEMA + ''.'' +

                                  TABLE_NAME AS ''TableName''

                           FROM information_schema.tables;'

GO

--SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'USE ?;SELECT @@SERVERNAME + ''.'' +

                                  DB_NAME() + ''.'' +

                                  SCHEMA_NAME(schema_id) + ''.'' +

                                  name AS ''TableName''

                           FROM sys.tables;'

GO

列出資料庫內所有表格名稱-V2008R2.png

 8.檢查資料庫

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'DBCC CHECKDB(?);'

GO

DBCC_CHECKDB-V2008R2.png

  

9.對每一個資料庫執行 DBCC UPDATEUSAGE 作業

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

USE master

GO

EXEC sp_MSforeachdb @command1="print '?' DBCC UPDATEUSAGE (?)"

GO

 

 

參考資料:

sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure http://www.kodyaz.com/articles/sp_Msforeachdb-example-list-all-database-files.aspx

 

Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general

The undocumented sp_MSforeachdb procedure 

http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

 

SQL SERVER curry

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

http://www.sqlservercurry.com/2009/04/6-common-uses-of-undocumented-stored.html

,

K 發表在 痞客邦 PIXNET 留言(0) 人氣()