SQL Server: How to tell if a database is a system database?

Just dived into Microsoft.SqlServer.Management.Smo.Database object (which is provided by Microsoft itself!) They simply do this using following statement:

CAST(case when dtb.name in ('master','model','msdb','tempdb') 
   then 1 
   else dtb.is_distributor end AS bit) AS [IsSystemObject]

In short: if a database is named master, model, msdb or tempdb, it IS a system db; it is also a system db, if field is_distributor = 1 in the view sys.databases.

Hope this helps

Jimmy

SQL Server Management Studio uses this

if you expand "System Databases" in "Object Explorer" (seen from wireshark):

SELECT dtb.name AS [Database_Name]
FROM master.sys.databases AS dtb
WHERE (CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=1)

For the sake of simplicity I removed irrelevant columns, removed orderby and replaced @_msparam_0 variable by its value 1