Maurice's Musings

Imports System.Data
' Refer to these DLLs:
'   microsoft.sqlserver.connectioninfo
'   Microsoft.SqlServer.Management.Sdk.Sfc
'   Microsoft.SqlServer.smo
'   Microsoft.SqlServer.sqlenum
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Common

Module Module1

    Sub Main()
        GetServers()
        Console.ReadLine()
    End Sub

    Private Function GetServers() As List(Of String)

        Dim servers As New List(Of String)
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)

        If dt.Rows.Count > 0 Then

            For Each dr As DataRow In dt.Rows

                Dim servername As String = dr("Name")

                If Not servers.Contains(servername) Then ' Only once per server

                    servers.Add(servername)
                    Console.WriteLine(servername)

                    Dim server As New Server(servername)
                    Try
                        ' Enumerate databases 
                        For Each db In server.Databases

                            If Not db.issystemobject Then
                                Console.WriteLine("  " & db.name)

                                ' Enumerate user tables
                                For Each t As Table In db.tables

                                    If Not t.IsSystemObject Then
                                        Console.WriteLine("    " & t.Name)
                                    End If

                                Next

                            End If

                        Next

                    Catch ex As Exception
                        Console.WriteLine("  " & ex.Message)
                        Console.WriteLine("  Maybe the SQL Server Browser Service isn't started on " & servername & "?")
                    End Try
                End If
            Next
        End If

        Return servers

    End Function


End Module