SQL Server – System Databases

Background

I was reviewing a database and noted that the distribution database is not named distribution, but TSRDistribution.

SQL Server Management Studio ( SSMS)

I wanted to see how SSMS is able to classify the database as a system database.

Image

ssms.outline.20190307.0836AM.PNG

Code

Outline

Ran a trace against the SQL Server Instance and noticed that SSMS issues the query pasted below.

What does the query do :-

  1. sys.databases
    • Name Match
      • Checks the database name and see if it matches
        • master
        • model
        • msdb
        • tempdb
    • Property match
      • Distributor
        • is_distributor

SQL


select
          dtb.[name]
        , dtb.database_id

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
        )

ORDER BY
    [name] asc

Summary

Confirmed that SSMS does not check against a specific rule, isSystem, to identify system databases.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s