Way to return a list of all databases on MS SQL server?

[XJ900]

Is there a way to return a list/names of every database that is on the MS SQL server?

 

[mrdenny]

Yes.

SELECT name
FROM sysdatabases
ORDER BY name

SELECT name
FROM sys.databases
ORDER BY name

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

This will work with either. It is essentially the same as mrdenny's, but if you use this, you won't have to worry about what happens when you upgrade to sql 2005.

[COLOR=blue]If[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](1), ServerProperty([COLOR=red]'ProductVersion'[/color])) = [COLOR=red]'8'[/color]
	[COLOR=blue]Select[/color] [COLOR=blue]Name[/color] [COLOR=blue]from[/color] master..sysdatabases
[COLOR=blue]Else[/color]
	[COLOR=blue]Select[/color] [COLOR=blue]Name[/color] [COLOR=blue]From[/color] sys.databases

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

cheeter.

fine, be that way.

if @@microsoftversion >= 150996343
    Select Name From sys.databases
Else
    Select Name from master..sysdatabases

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[XJ900]

Thank you, one more question:

Is there a way to do this with a DSN-less connection from a VB app?

 

[mrdenny]

DSNs aren't required to connect to a database. Just define all the SQL Server settings within the connection string and you should be able to connect.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[XJ900]

Thanks I'll give it a whirl.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com