Find System Databases in Catalog Views

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Jameson - 08 Feb 2010 21:50 GMT

Hi,

Um, how might one distinguish system databases from user databases in the
various catalog views of SQL 2005/8?

I guess it would be nice to know for SQL 2000 as well.

Signature

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Reply to this Message

Erland Sommarskog - 08 Feb 2010 22:59 GMT

> Um, how might one distinguish system databases from user databases in the
> various catalog views of SQL 2005/8?
>
> I guess it would be nice to know for SQL 2000 as well.

There is no direct flag, but if the database_id is <= 4 it's a system
database: master, model, tempdb or msdb.

To that comes distriubtion databases for replication, which can be
identified with sys.databases.is_distributor = 1.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Daniel Jameson - 11 Feb 2010 20:32 GMT

Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

>> Um, how might one distinguish system databases from user databases in the
>> various catalog views of SQL 2005/8?
[quoted text clipped - 6 lines]
> To that comes distriubtion databases for replication, which can be
> identified with sys.databases.is_distributor = 1.

Reply to this Message