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