How do I get a list of all the databases within the sever

Question

Post reply

How do I get a list of all the databases within the sever

mw_sql_developer

SSCoach

Points: 19445
More actions
January 19, 2015 at 12:42 pm

#313763

I see so many databases. Do I have to get to the master database first and then execute some SQL statement to get the list.

Viewing 7 posts - 1 through 7 (of 7 total)

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 1

use master

select top 100 * from sys.databases order by name

DONE! Do not reply

lmarkum SSCertifiable Points: 7989 More actions · Answer 2

Information about databases names and their options can be found in the sys.databases view.

select name from sys.databases.

From within a database context you can return data about the database objects from the sys.objects view.

For a more complete solution you can check out this post from Aaron Bertrand:

http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-views/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

mw112009 (1/19/2015)
use master
select top 100 * from sys.databases order by name
DONE! Do not reply

Not done if you have more than 100 databases.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

mw112009 (1/19/2015)
use master
select top 100 * from sys.databases order by name
DONE! Do not reply

"Do not reply"??? What's up with that? Heh... DO NOT REPLY... it's a rhetorical question. 😛

Also, since sys.databases is available in every database, you don't have to do the "USE master" thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Sorry what I meant when I said "DO NOT REPLY" is I had already found the solution. Didn't want to bother anyone.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

mw112009 (1/20/2015)
Sorry what I meant when I said "DO NOT REPLY" is I had already found the solution. Didn't want to bother anyone.

Ah... got it. Thanks for your consideration.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)