Solved

Enumerating Servers and databases on the server

Posted on 2006-06-22
7
342 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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