查詢 SQL主機 的所有資料庫名稱/檔案

sp_Msforeachdb 

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

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

執行下列語法可以將資料庫伺服器單一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

以下列出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. 顯示實體資料檔和記錄檔磁碟空間使用狀況

--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

4.顯示資料庫訊息

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb'USE ?; EXEC sp_helpdb ?;'

GO

5.檢查資料庫

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

EXEC sp_MSforeachdb 'DBCC CHECKDB(?);'

GO

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

--SQL2000、SQL2005、SQL2008、SQL2008R2適用

USE master

GO

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

GO