CodeItBetter Programming Another VB Programming Blog

'Database - How to Get the list of all SQL Servers in the Network
'Sometimes we need a list of all the SQL Servers present on the network. 

'here is a function that will return an array f all the SQL Servers present
'in the network. In order to run this function you will have to set reference to
'Microsoft SQLDMO Object Library (Goto Projects-->References and select Microsoft
'SQLDMO Object Library)

'Function : listSQLServers
'Input Parameter : None
'Return Value: Returns an Array that consists of the names of the SQLServers available
Public Function listSQLServers() As String()
    'set reference to Microsoft SQLDMO Object Library prior to using this code
    Dim sqlApp As New SQLDMO.Application
    Dim serverNameList As SQLDMO.NameList
    'an empty array that will hold all the servernames
    Dim serverNames() As String
    Dim loopCounter As Long
    'get all the SQL Servers available
    Set serverNameList = sqlApp.ListAvailableSQLServers
    'if no servers exist then return to the calling function
    If serverNameList.Count = 0 Then
        Set sqlApp = Nothing
        Set serverNameList = Nothing
        Exit Function
    End If
    'Redim the array with the proper count
    ReDim serverNames(serverNameList.Count)
    'loop through all the servers
    For loopCounter = 0 To serverNameList.Count
        'add to the array
        serverNames(loopCounter) = serverNameList.Item(loopCounter)
    Next
    listSQLServers = serverNames
ExitHere:
    'release objects
    Set sqlApp = Nothing
    Set serverNameList = Nothing
    Exit Function
End Function