Solved

Enumerating Servers and databases on the server

Posted on 2006-06-22
7
328 Views
Last Modified: 2010-04-23

Hi,

I want to list all servers available on a network, select a server, then list all the SQL Server databases on the selected server.

I can list the servers available on the network but am struggling to list all the databases on the selected server.

Any ideas? Code would be very useful!

Regards,

Alan
0
Comment
Question by:alan_ITG
  • 3
  • 2
  • 2
7 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16961889
1) .NET version?  2002, 2003 or 2005?

2) SQL Server versions?

Bob
0
 

Author Comment

by:alan_ITG
ID: 16966776

Hi Bob,

I am using VS 2005 and SQL Server 2000 & 2005 are the target systems.

Thsnks,


Alan
0
 
LVL 2

Expert Comment

by:Mystify
ID: 16967939
To find all the servers, it requires 2 things.

1. You need to add a project reference to SQLDMO
2. The SQL Server Browser service must be running. This is the service that allows it to be detected, without it running, the server will not be found on the network.




'Find SQL Servers on network:

        Dim Names As SQLDMO.NameList
        Dim SQLApp As SQLDMO.Application

        SQLApp = New SQLDMO.Application

        If My.Computer.Network.IsAvailable = False Then exit sub ' or do whatever you want to do if your network is not available.

        Names = SQLApp.ListAvailableSQLServers()

Now all the found servers will be in the Names object and you can loop through the array and get the names by Names.Item(Index #)
I have not been able to find a way to find SQL Servers that are not running their browser, nor have I been able to find servers without using SQLDMO

I initially thought about enumerating all the computers on a network and trying to connect, only do find that the datasource could use a PCNAME/SERVICENAME format, and the servicename can be anything, and I do not know how to get that manually. SQLDMO was the best solution.



To list all the databases on the server, you first must connect to it.
I am going to assume that you know how to do that.

Anyway, to list the databases just run the following query:

"SELECT * FROM master..sysdatabases"



0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 96

Accepted Solution

by:
Bob Learned earned 250 total points
ID: 16970297
That is why I ask about .NET version.  That way you can get the "best" advice.

SQLDMO is old school.  SMO is new technology that comes with 2005.  

Here is a class to describe what I mean:

' Reference:
'    A Quick Introduction to SQL Server Management Objects (SMO)  
'      http://www.yukonxml.com/articles/smo/

' Add a reference to Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo to the project for the following imports
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo

Public Class SqlServer

    ''' <summary>
    ''' The form of EnumerateServers for all machines in the network.
    ''' </summary>
    Public Overloads Shared Function EnumerateServers() As ServerInstance()
        Return EnumerateServers("")
    End Function

    ''' <summary>
    ''' Return a collection of server instance descriptors for all SQL Servers within a network
    ''' </summary>
    ''' <param name="computerName">Specify a computer name to target a particular machine</param>
    ''' <returns>An array of ServerInstance descriptor objects</returns>
    ''' <remarks>This method translates the DataTable to a list of objects with Intellisense.</remarks>
    Public Overloads Shared Function EnumerateServers(ByVal computerName As String) As ServerInstance()

        Dim tableServers As DataTable = Nothing

        If computerName.Length = 0 Then
            tableServers = SmoApplication.EnumAvailableSqlServers()
        Else
            tableServers = SmoApplication.EnumAvailableSqlServers(computerName)
        End If

        ' Create enough space for all the SQL Server instances.
        Dim list(tableServers.Rows.Count - 1) As ServerInstance

        ' Build the list of servers.
        For index As Integer = 0 To tableServers.Rows.Count - 1

            Dim row As DataRow = tableServers.Rows(index)

            Dim name As String = row("Name").ToString()
            Dim server As String = row("Server").ToString()
            Dim instance As String = row("Instance").ToString()
            Dim clustered As Boolean = row("IsClustered").ToString()
            Dim local As Boolean = row("IsLocal").ToString()

            Dim entry As New ServerInstance(name, server, instance, clustered, local)

            list(index) = entry

        Next index

        Return list
    End Function

    Public Class ServerInstance

        Public Sub New(ByVal name As String, ByVal server As String, ByVal instance As String, ByVal clustered As Boolean, ByVal local As Boolean)
            m_name = name
            m_server = server
            m_instance = instance
            m_clustered = clustered
            m_local = local
        End Sub

        Private m_name As String = ""
        Public Property Name() As String
            Get
                Return m_name
            End Get
            Set(ByVal value As String)
                m_name = value
            End Set
        End Property

        Private m_server As String = ""
        Public Property Server() As String
            Get
                Return m_server
            End Get
            Set(ByVal value As String)
                m_server = value
            End Set
        End Property

        Private m_instance As String = ""
        Public Property Instance() As String
            Get
                Return m_instance
            End Get
            Set(ByVal value As String)
                m_instance = value
            End Set
        End Property

        Private m_clustered As Boolean
        Public Property IsClustered() As Boolean
            Get
                Return m_clustered
            End Get
            Set(ByVal value As Boolean)
                m_clustered = value
            End Set
        End Property

        Private m_local As Boolean
        Public Property IsLocal() As Boolean
            Get
                Return m_local
            End Get
            Set(ByVal value As Boolean)
                m_local = value
            End Set
        End Property
    End Class

End Class

Bob
0
 
LVL 2

Expert Comment

by:Mystify
ID: 16970331
cool, even I will give it a look.

Old school or not, it works.  ;)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16971629
Actually, I am not saying that old school doesn't work, it's just that once you start looking at SMO, you'll see a considerable amount more than SQL-DMO.  Enumerating servers is only the tip of the iceberg, and a good place to start.

Bob
0
 

Author Comment

by:alan_ITG
ID: 16990487

Thanks all for the assistance!

Mistify - thanks for the help.  I decided to award the points to TheLearnedOne for the completeness of the solution.

regards,


Alan
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now