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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply