如何确定哪些数据库是和不是“系统”的？数据库 [英] How to determine which databases are and are-not "system" databases

问题描述

我正在查看sys.databases http://technet.microsoft.com/en-us/ library / ms178534.aspx [ ^ ]今天我想知道如何从用户数据库告诉系统数据库。到目前为止我想出的是将owner_sid与master的owner_sid进行比较。这似乎有效，但我很想知道是否有更好的方式。

I was looking at sys.databases http://technet.microsoft.com/en-us/library/ms178534.aspx[^] today and it occurred to me to wonder how to tell the "system" databases from the "user" databases. What I came up with so far is to compare the owner_sid to the owner_sid of "master". This seems to work, but I'm curious to know whether or not there is a "better" way.

推荐答案

SELECT * from sys.databases
where database_id<=6

这是系统数据库列表....

ID 5和6将是ReportServer和ReportServerTempDB



ID大于6的用户数据库。



希望，这会有所帮助...

This is the list of system databases....
ID 5 and 6 will be ReportServer and ReportServerTempDB

ID's greater than 6 will be user databases.

Hope, this will help...

我很想做这样的事情

I'd be tempted to do something like this

select *
from sys.databases
where database_id <= 4 or [name] in ('ReportServer','ReportServerTempDB');

看到主人时发现的问题

The problem I found when looking at the owner

select *
from sys.databases
where len(owner_sid) = 1

是如果我在开发服务器上找到的sa登录下创建的，它可以拔出用户数据库。

is that it can pull out user databases if created under the sa login as I found with our development server.

I找到了这个资源：



http://beyondrelational.com/modules/2/blogs/77/posts/11362/how-to-programmatically-识别-system-and-user-databases-on-a-sql-server-instance.aspx [ ^ ]



这可能有助于从用户数据库中确定系统数据库。

I found this resource:

http://beyondrelational.com/modules/2/blogs/77/posts/11362/how-to-programmatically-identify-system-and-user-databases-on-a-sql-server-instance.aspx[^]

That may be helpful in determining the system databases from the user ones.

这篇关于如何确定哪些数据库是和不是“系统”的？数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！