CODEWITHSUNDEEP
sql-serversql-server-2005system-databases
edosoft
edosoft

Reputation: 17271

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

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)

Upvotes: 6

Views: 6874

Answers (3)

Mohammad Anini
Mohammad Anini

Reputation: 5230

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

Upvotes: -1

Martin Smith
Martin Smith

Reputation: 453202

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.

Upvotes: 8

Thomas
Thomas

Reputation: 64645

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.

Upvotes: 3

Related Questions