October 27, 2009 at 9:50 am
Is there a proper method for identifying user vs system databases?
I've looked in sysdatases as well as sys.databases but couldn't find anything.
October 27, 2009 at 10:01 am
The dbid Column in sysdatabases should help you.
The DBID's 1 - 4 are always used by the System DB's and the DBID for the 5th System Database
SystemResorce DB is always 32767.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 27, 2009 at 12:38 pm
Thanks for the reply.
I've used that in the past but have also encountered times when dbid 5 is the distmodel database.
I was hoping there was a flag or some other method to identify a system db versus a user db.
October 27, 2009 at 4:32 pm
I don't know of any flag but the system databases always have the same name so you could use that to identify them (master,model,msdb,tempdb,distribution). anything else is a user database.
---------------------------------------------------------------------
October 28, 2009 at 3:55 am
I used the Profiler while refreshing the System Databases branch in the Object Explore of SSMS and the following (partial) statement came along:
SELECT
dtb.name AS [Database_Name]
/* More columns */
FROM
master.sys.databases AS dtb
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=1)
ORDER BY
[Database_Name] ASC
So it seems the only way to determine if a database is a system database is to look at the name and the is_distributor bit.
Peter
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy