Programmatically List All SQL Servers And Their Databases Using SMO

Basically there are 2 ways to do this. The first one is by using SQL-DMO (Distributed Management Objects) which is a set classic classes that were used to programmatically manipulate and interact with SQL Servers. Please reffer to this article by MisterClay to see this classes in action.


On the other hand, there is SQL SMO (SQL Management Objects) the new set of classes designed to be the next generation of classes to serve as replacement to the old SQL-DMO.


In this article, i’ll show you how to programmatically retrieve a list of available SQL Servers on the network and then retrieve the underlying databases on that SQL Server.


The first thing that we need to do is add references to our project. We’ll need to have refence on this two libraries:


Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.ConnectionInfo


This libraries contain the classes that we need to use for this demo.


Next we need 2 listboxes, the first listbox will contain the list of servers and the second one if for its databases. Next we need to wire-up the list of SQL Servers to the first listbox. We’ll put this code on the Form_Load event of our application


Here’s the C# Code:


private void MainForm_Load(object sender, EventArgs e)
{
   //just list local servers, set to false if you want to see all servers
   DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
   lstServers.ValueMember = “Name”;
   lstServers.DataSource = dataTable;
}


Here’s the VB.NET


Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   ’just list local servers, set to false if you want to see all servers
   Dim dataTable = SmoApplication.EnumAvailableSqlServers(True)
   lstServers.ValueMember = “Name”
   lstServers.DataSource = dataTable
End Sub


The SMOApplication class contains a method called EnumAvailableSQLServers. This method returns a DataTable that contains a list of the available servers. This method accepts a boolean parameter wherein you can specify if you want to list all servers in the network or just list down those in the local machine.


Next we need to create a SelectedIndexChange for our lstServers control. This is so that we can list the databases from the selected server. Please reffer to the code below:


C# Version


private void lstServers_SelectedIndexChanged(object sender, EventArgs e)
{
   lstDatabases.Items.Clear();
   if (lstServers.SelectedIndex != -1)
   {
      string serverName = lstServers.SelectedValue.ToString();
      Server server = new Server(serverName);
      try
      {
         foreach (Database database in server.Databases)
         {
            lstDatabases.Items.Add(database.Name);
         }
      }
      catch (Exception ex)
      {
         string exception = ex.Message;
      }
   }
}


VB.NET Version


Private Sub lstServers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstServers.SelectedIndexChanged
   lstDatabases.Items.Clear()
   If lstServers.SelectedIndex <> -1 Then
      Dim serverName As String = lstServers.SelectedValue.ToString()
      Dim server As Server = New Server(serverName)
      Try
         For Each database As Database In server.Databases
            lstDatabases.Items.Add(database.Name)
         Next
      Catch ex As Exception
         Dim exception As String = ex.Message
      End Try
   End If
End Sub



And thats how easy it is. Pretty slick huh? I wish i had this back in Visual basic 6!


Get the code here: SQLDMODemo.zip (29.2 KB)

Leave a Reply