Geek Answers Handbook

Can I query system databases on a SQL server without using names?

When performing a query on several databases on an SQL server (2005+), sometimes it is sometimes necessary for me to exclude the system database (master, model, tempdb, msdb and distribution)

Is there any other way to filter them besides

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

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

[UPDATE] sample query that I use to search 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] Does this sound like 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)
+5
source share
3 answers

SQL Profiler node .

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

, , dbid is_distributor.

+6

, . (master, model, msdb, tempdb), DBID > 4. DBID, , DBID.

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

More articles:


All Articles