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.
