{"id":596,"date":"2012-08-13T22:17:45","date_gmt":"2012-08-13T20:17:45","guid":{"rendered":"http:\/\/www.calvert.ch\/maurice\/?p=596"},"modified":"2015-02-22T21:35:27","modified_gmt":"2015-02-22T20:35:27","slug":"enumerate-sql-servers-their-databases-and-tables","status":"publish","type":"post","link":"https:\/\/www.calvert.ch\/maurice\/2012\/08\/13\/enumerate-sql-servers-their-databases-and-tables\/","title":{"rendered":"Enumerate SQL Servers, their databases and tables"},"content":{"rendered":"<pre>Imports System.Data\r\n' Refer to these DLLs:\r\n'   microsoft.sqlserver.connectioninfo\r\n'   Microsoft.SqlServer.Management.Sdk.Sfc\r\n'   Microsoft.SqlServer.smo\r\n'   Microsoft.SqlServer.sqlenum\r\nImports Microsoft.SqlServer.Management.Smo\r\nImports Microsoft.SqlServer.Management.Sdk.Sfc\r\nImports Microsoft.SqlServer.Management.Common\r\n\r\nModule Module1\r\n\r\n    Sub Main()\r\n        GetServers()\r\n        Console.ReadLine()\r\n    End Sub\r\n\r\n    Private Function GetServers() As List(Of String)\r\n\r\n        Dim servers As New List(Of String)\r\n        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)\r\n\r\n        If dt.Rows.Count &gt; 0 Then\r\n\r\n            For Each dr As DataRow In dt.Rows\r\n\r\n                Dim servername As String = dr(\"Name\")\r\n\r\n                If Not servers.Contains(servername) Then ' Only once per server\r\n\r\n                    servers.Add(servername)\r\n                    Console.WriteLine(servername)\r\n\r\n                    Dim server As New Server(servername)\r\n                    Try\r\n                        ' Enumerate databases \r\n                        For Each db In server.Databases\r\n\r\n                            If Not db.issystemobject Then\r\n                                Console.WriteLine(\"  \" &amp; db.name)\r\n\r\n                                ' Enumerate user tables\r\n                                For Each t As Table In db.tables\r\n\r\n                                    If Not t.IsSystemObject Then\r\n                                        Console.WriteLine(\"    \" &amp; t.Name)\r\n                                    End If\r\n\r\n                                Next\r\n\r\n                            End If\r\n\r\n                        Next\r\n\r\n                    Catch ex As Exception\r\n                        Console.WriteLine(\"  \" &amp; ex.Message)\r\n                        Console.WriteLine(\"  Maybe the SQL Server Browser Service isn't started on \" &amp; servername &amp; \"?\")\r\n                    End Try\r\n                End If\r\n            Next\r\n        End If\r\n\r\n        Return servers\r\n\r\n    End Function\r\n\r\n\r\nEnd Module\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Imports System.Data &#8216; Refer to these DLLs: &#8216; microsoft.sqlserver.connectioninfo &#8216; Microsoft.SqlServer.Management.Sdk.Sfc &#8216; Microsoft.SqlServer.smo &#8216; 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 <a href='https:\/\/www.calvert.ch\/maurice\/2012\/08\/13\/enumerate-sql-servers-their-databases-and-tables\/' class='excerpt-more'>[&#8230;]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,11],"tags":[35],"class_list":["post-596","post","type-post","status-publish","format-standard","hentry","category-programming","category-technology","tag-sql","category-47-id","category-11-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/comments?post=596"}],"version-history":[{"count":5,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/596\/revisions"}],"predecessor-version":[{"id":814,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/596\/revisions\/814"}],"wp:attachment":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/media?parent=596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/categories?post=596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/tags?post=596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}