How to programmatically identify system and user databases on a SQL Server instance


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:

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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s