How to Differentiate System and User Databases In SQL Server

This question is frequently asked in many forums and the most popular
answer is to use the DB_ID() function as shown below.

So if you want to get only the system databases you can use
DB_ID(databasename) < 5 in your where clause. This work around is used since
there is no dedicated column in sys.databases or sys.sysdatabases system views.

However, there is an issue if you have configured
replication.  When replication is configured a new database is created, by
default  that database will be distributor but the user can configure it.

The following query will return whether the database is a system database or a user database.

select name,case when name in ('master','model','msdb','tempdb')  

then 1 else is_distributor end is_system_database from 


The above query will will work with
all the SQL Server editions. This is the mechanism SQL Server uses to identify
system databases in the backup in maintenance tasks.


No comments yet... Be the first to leave a reply!