是否可以在不使用名称的情况下查询 SQL Server 中的系统数据库？
当在 SQL Server (2005+) 中对多个数据库进行查询时，我发现有时需要排除系统数据库（master、model、tempdb、msdb 和 distribution）
之外，还有其他方法可以过滤这些数据库吗？
where name not in (''master', 'model', 'tempdb', 'msdb', 'distribution')
除了我查看过 sys .databases 和 master.dbo.sysdatabases （不一样！）
[更新] 一个示例查询，我用它来查找启用了日志传送的数据库
select d.name, p.last_backup_date, s.secondary_server, s.secondary_database
from sys.databases d
left outer join msdb..log_shipping_primary_databases p on p.primary_database = d.name
left outer join msdb..log_shipping_primary_secondaries s on s.primary_id = p.primary_id
where name not in ('model','master','tempdb','distribution','msdb')
order by d.name
[更新] 这似乎是“最不坏”的方式，除非其他人有更好的方法？
SELECT * FROM
master.sys.databases AS dtb
WHERE (dtb.database_id < 5 or dtb.is_distributor = 1)
When doing a query over several databases in SQL server (2005+) I find it sometimes necesary to exclude the system database (master, model, tempdb, msdb and distribution)
Is there any OTHER way to filter these besides
where name not in (''master', 'model', 'tempdb', 'msdb', 'distribution')
I've looked at sys.databases and master.dbo.sysdatabases (not the same!)
[UPDATE] an example query which I use to look for databases that have logshipping enabled
select d.name, p.last_backup_date, s.secondary_server, s.secondary_database
from sys.databases d
left outer join msdb..log_shipping_primary_databases p on p.primary_database = d.name
left outer join msdb..log_shipping_primary_secondaries s on s.primary_id = p.primary_id
where name not in ('model','master','tempdb','distribution','msdb')
order by d.name
[UPDATE] This seems to be the 'least bad' way, unless someone else has a better way?
SELECT * FROM
master.sys.databases AS dtb
WHERE (dtb.database_id < 5 or dtb.is_distributor = 1)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我运行了 SQL Profiler 并刷新了 Management Studio 中的系统数据库节点。它使用查询
所以我想您可以结合
dbid和is_distributor检查。I ran SQL Profiler and refreshed the system databases node in management studio. It uses the query
So I guess you could combine the
dbidandis_distributorchecks.除了搜索名字之外，没有其他安全的方法。如果您只想过滤掉四个主要数据库（master、model、msdb、tempdb），您可以安全地过滤
DBID > 4.。然而，分发数据库将像普通数据库一样获得一个 DBID，因此您不能依赖它的 DBID。There is no safe means beyond searching on the name. If you only wanted to filter out the four main databases (master, model, msdb, tempdb), you can safely filter on
DBID > 4. However the distribution database will get a DBID like a normal database and thus you cannot rely on its DBID.