sp_msforeachdb 未列出 SQL Server 實例上的所有數據庫

我sp_msforeachdb用來列出我伺服器上的所有數據庫。

我知道它不受支持，但我想知道為什麼它沒有列出我安裝的所有數據庫。

這就是我執行它的方式：

set run="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQL2008 -E -h -1 -Q  
%run% "exec sp_msforeachdb 'select ''?'''"

輸出：

master 
tempdb 
model 
msdb 
balance166

SSMS 顯示的數據庫：

master 
tempdb 
model 
msdb 
balance166
BOON205
KAB205

兩者BOON0205和KAB205都已使用以下命令恢復：

%run% "..."where"..."是這樣的語句在一行中：

RESTORE DATABASE [BOON205] FROM DISK = N'C:\Data\Backup\Balance205.bak'
 WITH REPLACE,
 STATS = 10,
 MOVE N'Balance166' to N'C:\Data\Balance205.mdf', 
 MOVE N'Balance166_log' to N'C:\Data\Balance205_log.ldf';

恢復顯然有效：我可以很好地連接到這些數據庫。

但sp_msforeachdb沒有列出它們。

為什麼？

我可以從哪裡開始了解？

編輯：環境

• SQL Server 2008 版本 10.0.1600.22 RTM 標準版
• Windows Server 2008 標準版
• 登錄使用者在域中，但在這台機器上是本地管理員，並使用“GRANT CONTROL SERVER”作為 Windows 使用者添加到 SQL Server 數據庫安全性

sys.databases沒關係：

C:\bin>%sqlrun% -Q "select name from sys.databases"
name
-----------------------------------------------------------------------------
master
tempdb
model
msdb
balance166
Balance205
KAB205
BOON205
(8 rows affected)

has_dbaccess可以，但status可能不是。需要檢查一下：

C:\bin>%sqlrun% -Q "select cast(name as varchar(10)), status, cast(status as varbinary(8)), DATABASEPROPERTY(name, 'issingleuser') as issingleuser, has_dbaccess(name) as has_dbaccess from master.dbo.sysdatabases"
          status                 issingleuser has_dbaccess
---------- ----------- ---------- ------------ ------------
master           65544 0x00010008            0            1
tempdb           65544 0x00010008            0            1
model            65536 0x00010000            0            1
msdb             65544 0x00010008            0            1
balance166       65536 0x00010000            0            1
Balance205  1073807361 0x40010001            0            1
KAB205      1073807361 0x40010001            0            1
BOON205     1073807361 0x40010001            0            1

這不是狀態，因為所有這些都是DATABASEPROPERTYEX('master', 'Status')回報ONLINE。使用此處

找到的狀態程式碼列表和sp_msforeachdb 的定義，我將其分解為這樣，排除了問題：status

        8 0x00000008 - 'trunc. log on chkpt'
    65536 0x00010000 - 'online'
    65544 0x00010008 - 65536 + 8
1073741824 0x40000000 - 'invalid login'
1073807361 0x40010001 - 1073741824 + 65536 + 8

因此列表中的最後 4 個數據庫具有“無效登錄”。

是時候研究安全和權利了……

——杰倫

SQL Server Management Studio 對 sys.databases 目錄視圖進行查詢。所有數據庫都列在該 DMV 中。儲存過程 sp_msforeachdb 具有呼叫函式 has_dbaccess() 來解析其數據庫列表的邏輯。它顯然不想嘗試對您無權訪問的數據庫執行命令。您是否可以使用用於執行查詢的帳戶訪問所有相關數據庫？

ms_foreachdb 在 SQL Server 2005 或更高版本中無法正常工作 - 但如果您從該儲存過程中複製出邏輯，並將游標類型更改為 INSENSITIVE，則此未記錄的儲存過程可以正常工作。但是，您最好使用自己的程式碼進行將來的校對。

引用自：https://serverfault.com/questions/162656