How to determine which databases are and are-not "system" databases

Question

3.67/5 (2 votes)

See more:

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.

Posted 10-Dec-13 14:23pm

PIEBALDconsult

Updated 10-Dec-13 16:58pm

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 10-Dec-13 20:57pm

System databases or system tables?
—SA

PIEBALDconsult 10-Dec-13 21:23pm

Just as it says.

Sergey Alexandrovich Kryukov 10-Dec-13 22:53pm

Just checking... I could see it because your link points to "sys.databases", sorry for distracting...
—SA

PIEBALDconsult 10-Dec-13 22:57pm

No problem; not getting any other bites.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jain Nishant · Answer 1 · 2013-12-10T18:16:00

Solution 1

SQL

SELECT * from sys.databases
where database_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...

Posted 10-Dec-13 18:16pm

Jain Nishant

Comments

PIEBALDconsult 11-Dec-13 7:21am

Sure, but it doesn't seem definitive; I think checking the owner is more accurate, they could add more "system" databases in the future.

Actually, looking at one of my databases, I see that 5 and 6 are for Report Services*, but on another server, where that isn't installed, my first "user" database is number 5.

* And they're not listed under "System databases" (and not owned by sa), but I wish they were. Same with SSISDB, but I see that that one is owned by our DBA rather than sa. This may be a more difficult question than I thought.

Ha ha ha! I just set my Junk database to be owned by sa so it breaks my test. As to yours, I guess the best you can do is use <=4 rather than 6.

Simon_Whale · Answer 2 · 2013-12-11T05:30:00

Solution 2

I'd be tempted to do something like this

SQL

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

The problem I found when looking at the owner

SQL

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

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

Posted 11-Dec-13 5:30am

Simon_Whale

Comments

PIEBALDconsult 11-Dec-13 12:28pm

Yeah, so maybe if the DBA ensures that only "system" databases (which we can consider to include Report Services and SSISDB) are owned by sa, then that could work.

Ron Beyer · Answer 3 · 2013-12-11T05:52:00

Solution 3

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.

Posted 11-Dec-13 5:52am

Ron Beyer

Comments

PIEBALDconsult 11-Dec-13 12:32pm

Thanks. Very good, but still doesn't address Report Services and SSISDB. Not to mention third-party databases -- we just started using BiXpress and it creates a database.