SQL Server:如何判断数据库是否为系统数据库? [英] SQL Server: How to tell if a database is a system database?

问题描述

我知道到目前为止(至少在 MSSQL 2005 之前)，系统数据库是 master、model、msdb 和 tempdb.

I know that so far (until MSSQL 2005 at least), system databases are master, model, msdb and tempdb.

事情是，据我所知，这不能保证将来会保留.sys.databases 视图和 sys.sysdatabases 视图都没有告诉我一个数据库是否被视为系统数据库.

Thing is, as far as I can tell, this is not guaranteed to be preserved in the future. And neither the sys.databases view nor the sys.sysdatabases view tell me if a database is considered as a system database.

有什么地方可以获得这些信息(无论一个数据库是否被认为是系统数据库)?

Is there someplace where this information (whether a database is considered a system database or not) can be obtained?

推荐答案

刚刚深入Microsoft.SqlServer.Management.Smo.Database 对象(由微软自己提供！)他们只是使用以下语句来执行此操作:

Just dived into Microsoft.SqlServer.Management.Smo.Database object (which is provided by Microsoft itself!) They simply do this using following statement:

CAST(case when dtb.name in ('master','model','msdb','tempdb') 
   then 1 
   else dtb.is_distributor end AS bit) AS [IsSystemObject]

简而言之:如果一个数据库被命名为master、model、msdb 或tempdb，它是一个系统数据库；它也是一个系统数据库，如果视图 sys.databases 中的字段 is_distributor = 1.

In short: if a database is named master, model, msdb or tempdb, it IS a system db; it is also a system db, if field is_distributor = 1 in the view sys.databases.

希望能帮到你

吉米

这篇关于SQL Server:如何判断数据库是否为系统数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！