![sp_msforeachdb 未列出 SQL Server 实例上的所有数据库](https://linux22.com/image/519409/sp_msforeachdb%20%E6%9C%AA%E5%88%97%E5%87%BA%20SQL%20Server%20%E5%AE%9E%E4%BE%8B%E4%B8%8A%E7%9A%84%E6%89%80%E6%9C%89%E6%95%B0%E6%8D%AE%E5%BA%93.png)
我用它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
--杰伦