Is it possible to query the system databases in SQL server without using the names?

Question

When doing a query over several databases in SQL server (2005+) I find it sometimes necesary to exclude the system database (master, model, tempdb, msdb and distribution)

Is there any OTHER way to filter these besides

where name not in (''master', 'model', 'tempdb', 'msdb', 'distribution')

I've looked at sys.databases and master.dbo.sysdatabases (not the same!)

[UPDATE] an example query which I use to look for databases that have logshipping enabled

select d.name, p.last_backup_date, s.secondary_server, s.secondary_database
from sys.databases d
    left outer join msdb..log_shipping_primary_databases p on p.primary_database = d.name
    left outer join msdb..log_shipping_primary_secondaries s on s.primary_id = p.primary_id
where name not in ('model','master','tempdb','distribution','msdb')
order by d.name

[UPDATE] This seems to be the 'least bad' way, unless someone else has a better way?

SELECT * FROM 
master.sys.databases AS dtb 
WHERE (dtb.database_id < 5 or dtb.is_distributor = 1)

Could you give us an example of what queries you're running? — Codesleuth, Aug 03 '10 at 14:04

score 8 · Accepted Answer · answered Aug 03 '10 at 14:34

I ran SQL Profiler and refreshed the system databases node in management studio. It uses the query

...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)

So I guess you could combine the dbid and is_distributor checks.

score 3 · Answer 2 · answered Aug 03 '10 at 14:23

3

There is no safe means beyond searching on the name. If you only wanted to filter out the four main databases (master, model, msdb, tempdb), you can safely filter on DBID > 4. However the distribution database will get a DBID like a normal database and thus you cannot rely on its DBID.

answered Aug 03 '10 at 14:23

Thomas

63,911
12
95
141

Thanks. I noticed the distribution DBID as well, it's the underlying reason for my question. – edosoft Aug 03 '10 at 14:31

score -1 · Answer 3 · answered Mar 21 '16 at 10:33

-1

select
    *
from
    sys.databases
where
    name in ('master','model','msdb','tempdb')
    or is_distributor = 1

answered Mar 21 '16 at 10:33

Mohammad Anini

5,073
4
35
46

question clearly states "Is it possible to query the system databases in SQL server without using the names?" , also same (deleted) answer you posted here several years after the fact: https://stackoverflow.com/questions/1819095/sql-server-how-to-tell-if-a-database-is-a-system-database – Mitch Wheat Jan 10 '19 at 01:19

Is it possible to query the system databases in SQL server without using the names?

3 Answers3