Getting a list of SQL 2005 Servers and The Databases in them

10-02-2006, 08:48 AM

Aquila

Contributor

Join Date: Oct 2002

Location: Ocala, Fl.

Posts: 597

Getting a list of SQL 2005 Servers and The Databases in them

I know it's poor form to use VB6 as a reference for .net/2005 questions, but I really don't know of any other way.

Back using VB 6 I was able to get a list of SQL Servers by using the
SQLDMO.NameList object, from there I could easily get the databases held in each server.

Is there a similar object or method of sql, sqlclient, etc I can use to do the same thing?

I need to detect running SQL Server 2005 databases and the databases attached to them. regardless of if the db is local or on a network, and do that via user interface (I need users to be able to select their databases on different servers from the same app without them knowing how to create their own connection string)

__________________
Plotting to take over the world since 1978

10-02-2006, 09:03 AM

Aquila

Contributor

Join Date: Oct 2002

Location: Ocala, Fl.

Posts: 597

AHA! I found it
Microsoft.SQLServer.Management.SMO

is the .net/2005 way to do it

I'll publish a working demo on how to work with it once I figure it all out, I can't be the only one that needs to use this.

__________________
Plotting to take over the world since 1978

10-02-2006, 10:25 AM

Aquila

Contributor

Join Date: Oct 2002

Location: Ocala, Fl.

Posts: 597

Here's how you get a list of servers.
//Assume that there is a form with a datagridview object named dgvServers

Code:

'this process takes a little time so set the cursor to a wait cursor
    Me.Cursor = Cursors.WaitCursor
    'The EnumAvailableSQLServers method returns a datatable
    'so dim a datatable and execute the method using the second override (show local only) set to false
    'so I can see all the databases regardless of if they are on the network or otherwise.
    Dim dtbl As DataTable = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(False)
    'Pipe the results into a dataview for use.
    Me.dgvServers.DataSource = dtbl
    'Return the cursor to normal
    Me.Cursor = Cursors.Default

You'll also have to add a reference to Microsoft.SqlServer.Management.Smo

Simple enough!

__________________
Plotting to take over the world since 1978

10-02-2006, 10:35 AM

Aquila

Contributor

Join Date: Oct 2002

Location: Ocala, Fl.

Posts: 597

aaaaaand....

There is an even simpler way... using the SMO.RegisterdServer class

Someone's already written a much better tutorial than I ever could at http://www.sqldbatips.com/showarticle.asp?ID=45

__________________
Plotting to take over the world since 1978