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
sys.databases

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.




Related Articles :

  • No Related Articles Found
Uncategorized

One Response to “How to Differentiate System and User Databases In SQL Server”

  1. select name , database_id , owner_sid
    from sys.databases

    The may understand the owner_sid different from the user database

    Chandrasekaran

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |