在Master資料庫中的系統預儲程序中可以找到這個指令；透過這個指令可以輕鬆取得每一個資料庫的詳細資訊，也可透過這個指令執行資料庫的維護作業。
目前SQL Server 都是透過下列指令，來取得資料庫檔案的相關訊息。
USE 資料庫名稱
GO
SELECT * FROM sysfiles
GO 其中 sysfiles 資料表：
--在SQL Server 2000中，sysfiles位於每一個資料庫的系統資料表格中
--在SQL Server 2005中，sysfiles位於每一個資料庫的檢視表中的系統檢視表
--在SQL Server 2008中，sysfiles位於每一個資料庫的檢視表中的系統檢視表
使用 master 資料庫，查詢相關的檔案資料訊息
如果需要列出資料庫伺服器中所有資料庫的檔案資料訊息，就必須在每個資料庫執行上列指令，而無法在單次查詢中就取得所有訊息，透過sp_MSforeachdb指令可以幫助你將每個資料的訊息彙總起來。
執行sp_MSforeachdb必須提供參數，若未提供錯誤訊息如下所示：
在SQL Server 2008R2中執行sp_MSforeachdb未提供參數的錯誤訊息
接著藉由sp_MSforeachdb指令，幫助我們執行指令SELECT * FROM sysfiles，完整指令如下：
--SQL2000、SQL2005、SQL2008、SQL2008R2適用 EXECsp_MSforeachdb 'USE [?];SELECT * FROM sysfiles;' 直接顯示彙總資料庫檔案 2008R2 執行結果
原本我們透過指令，只能對單一資料庫進行處理。
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中的所有資料庫名稱。
執行下列語法可以將資料庫伺服器單一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
如有必要可以將上列的暫存資料表改為實體資料表，可用來設計自己的資料庫監控系統，用來觀察與紀錄資料庫的使用狀況。
以下列出sp_MSforeachdb常見的應用
1.列出資料庫實體名稱 --SQL2000、SQL2005、SQL2008、SQL2008R2適用
EXEC sp_MSforeachdb 'USE [?];EXEC sp_helpfile;'
GO 
2.顯示資料庫使用的磁碟空間大小
--SQL2000、SQL2005、SQL2008、SQL2008R2適用
EXEC sp_MSforeachdb 'USE [?];EXEC sp_spaceused;'
GO 
3.將資料庫的Page_VERIFT選項設定為CHECKSUM
--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 
5.顯示資料庫訊息
--SQL2000、SQL2005、SQL2008、SQL2008R2適用
EXEC sp_MSforeachdb 'USE ?; EXEC sp_helpdb ?;'
GO 
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 
--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 
8.檢查資料庫
--SQL2000、SQL2005、SQL2008、SQL2008R2適用
EXEC sp_MSforeachdb 'DBCC CHECKDB(?);'
GO 
9.對每一個資料庫執行 DBCC UPDATEUSAGE 作業
--SQL2000、SQL2005、SQL2008、SQL2008R2適用
USE master
GO EXEC sp_MSforeachdb @command1="print '?' DBCC UPDATEUSAGE (?)"
GO 參考資源：
沒有留言:
張貼留言