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

sql-server-2005 information-schema
Vinko Vrsalovic picture Vinko Vrsalovic · Nov 30, 2009 · Viewed 14.1k times · Source

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?

Answer

Jimmy picture Jimmy · Mar 13, 2012

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

Related questions

Information schema and Primary Keys

How do I just print out a 'primary key' for the column with the primary key? I get 'primary key' for all the columns if the table has a primary key, instead of the one column with the primary key …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More