I am sure that when preparing installers for your products, you would have come across a requirement to filter out the system databases deployed on a SQL Server instance when presenting a list of available SQL Servers to the user.

System databases, per Books On Line (http://msdn.microsoft.com/en-us/library/ms178028.aspx) are the following:

• master
• msdb
• model
• tempdb
• resource

In addition, the distribution database in a replication topology is also a system database (It appears within the “System Database” node within object explorer in SSMS).

It appears that there is always a lot of confusion around the question – “How to identify system databases programmatically?”. The unfortunate answer to this question is simply that Microsoft SQL Server does not have any documented method of identifying system databases besides filtering on the database name.

I use the following query (even in production code) and it has served me well. I therefore share it with you today:

--*********************************************************
--WARNING:
--These queries are provided "as-is" and without warranty
--The author, BeyondRelational.com and Microsoft are not
--responsible for damage caused by misuse of this query
--*********************************************************

--Provides a list of system databases
SELECT sdb.database_id,
       sdb.name
FROM sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 1

--Provides a list of user databases
select sdb.database_id,
       sdb.name
from sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 0

Note that this list does not contain the Resource database. That’s simply because it is not directly accessible as a separate database! The resource database, as you know manifests itself as the “sys” schema in any database and therefore cannot be queried outside of this environment.

I hope that the above mentioned queries are helpful to you.

Until we meet next time,

Be courteous. Drive responsibly.