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

information-schema, sql-server-2005

Solution

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

Problem

I know that so far (until MSSQL 2005 at least), system databases are master, model, msdb and tempdb. Thing is, as far as I can tell, this is not guaranteed to be preserved in the future. And neither the sys.databases view nor the sys.sysdatabases view tell me if a database is considered as a system database. Is there someplace where this information (whether a database is considered a system database or not) can be obtained?

Original source