☰

sp_msforeachdb 未列出 SQL Server 实例上的所有数据库

tag-icon • tag-icon windows-server-2008 sql-server sql-server-2008 database
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% "..."有"..."如下语句：

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 个数据库有“无效登录”。
是时候研究安全性和权限了……

--杰伦

答案1

SQL Server Management Studio 对 sys.databases 目录视图执行查询。所有数据库都列在该 DMV 中。存储过程 sp_msforeachdb 具有调用函数 has_dbaccess() 来解析其数据库列表的逻辑。它显然不想尝试对您无权访问的数据库运行命令。您是否可以使用运行查询的帐户访问所有相关数据库？

答案2

ms_foreachdb 在 SQL Server 2005 或更高版本中无法正常工作 - 但如果您从此存储过程复制逻辑，并将游标类型更改为 INSENSITIVE，此未记录的存储过程可以正常工作。但是，您最好使用自己的代码以备将来使用。

答案3

解决了！0x4000000数据库状态上的位是自动关闭DB 的设置。
AutoClose很糟糕，不仅因为它混淆了 ms_foreachdb，还因为它经常会使您的性能变差。好消息：它已列入“淘汰名单”。

帮助数据库可以将状态解析为可读的形式，并揭示数据库备份 源代码错误地将标志解释AutoClose为InvalidLogin:-)

显示的内容如下sp_helpdb（向右滚动可查看 Balance166 和 Balance205 之间的差异）：

Balance166 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics
Balance205 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAutoCreateStatistics, IsAutoUpdateStatistics

关闭 AutoClose 很容易：

USE [master]
GO
ALTER DATABASE [Balance205] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

并产生以下设置：

name       status  Xstatus    DBStatus   issingleuser has_dbaccess
---------- ------- ---------- ---------- ------------ ------------
Balance166   65536 0x00010000 ONLINE                0            1
Balance205   65536 0x00010000 ONLINE                0            1

--杰伦

相关内容