Query a List of user databases

Question

Post reply

Query a List of user databases

David Kranes

SSCrazy

Points: 2523
More actions
July 3, 2008 at 8:35 am

#187796

Hello, is there a way to query sys.databases or some other view or table for just user databases and NOT system databases?
Thank you,
-David
Best Regards,
~David

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Not sure what exactly you want but I usually use WHERE database_id > 4.

If you want to execute a query on all user databases you can use this:

EXEC master..sp_MSForeachdb 'USE [?]IF DB_ID(''?'')>4 SELECT top 10 * from sysobjects'

[font="Verdana"]Markus Bohse[/font]

David Kranes SSCrazy Points: 2523 More actions · Answer 2

The WHERE database_id > 4 works fine.

Thank you Markus.

-David

Best Regards,

~David

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

Dont depend on the database id since there may be new system databases introduced in the future. use sys.databases.owner_sid instead.

[font="Courier New"]select ... from sys.databases where owner_sid != 1[/font]

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Hi

Check out the below link

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3501590&SiteID=17

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

David Kranes SSCrazy Points: 2523 More actions · Answer 5

Vj, excellent point. Thank you.

-David

Best Regards,

~David

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 6

[font="Courier New"]select ... from sys.databases where owner_sid != 1[/font]

Antonio,

while I agree it's not 100% safe to rely on db_id, the problem with you're attempt is that it will only give you the databases which have an owner other than sa. But in a lot of companies it is seen as best practice to make the sa account owner of all databases. In that case you're query wouldn't return any database at all.

[font="Verdana"]Markus Bohse[/font]

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 7

i've seen rare instances where the system dbs were not ids 1-4 due to restores of corrupted dbs. and in the shops i have influence over SA never owns any database for that very reason (impossible to distinguish between system and non-system dbs). (and only a couple of dbas have the sa password.)

i know lots of folks make every db owned by SA to make login/id mgmt easier, but i consider that (dare i say it) lazy. (just because something's a pita, doesn't mean it should be avoided.)

see microsoft's best practices here (page 18):

Best practices for database ownership and trust

•Have distinct owners for databases; not all databases should be owned by sa.

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

So far as I know, the only reliable way is:

Select * From sys.databases Where [name] NOT IN ('master', 'tempdb', 'model', 'msdb')

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]