CODEWITHSUNDEEP
sql-serversql-server-2005
Cute
Cute

Reputation: 14021

How to display user databases only in sqlserver?

how to display user databases in sqlserver

i queried as select sys.databases it displays all including msdb temp and all.

i need only user cretaed databases.

is there any query like : select * from sys.databases where type='u' ??

Help me.

Thanks in Advance

Upvotes: 2

Views: 3321

Answers (3)

Abdullah Ilgaz
Abdullah Ilgaz

Reputation: 739

You should specify them with their owner name. In order to do that, you should join sys.databases with sys.server_principals.

Recent versions can have ReportServer databases as well with contains $ sign in name.

select
  d.name
  ,d.database_id
from
  sys.databases d
join
  sys.server_principals p
  on p.sid = d.owner_sid
where
  p.name <> 'sa' and d.name not like '%$%';

Upvotes: 1

fretje
fretje

Reputation: 8382

Or per this thread:

select * from sys.sysdatabases where dbid>4 and [name] not like '$'

Upvotes: 0

Robin Day
Robin Day

Reputation: 102578

Is there a reason it needs to be more advanced than just this?

SELECT * FROM sysdatabases WHERE name NOT IN('master', 'tempdb', 'model', 'msdb')

Upvotes: 3

Related Questions