Get local and remote SQL Server Instances

In this post I will show you how to get list of remote and local SQL Server Instances. When you want to do this, you can use SQL SMO.SMO provides a method for enumerating SQL Server instances on network. Method EnumAvailableSqlServers returns DataTable with a few columns. In this case we will use Name column. This method has one parameter LocalOnly of boolean data type. When this parameter is true, than method returns only local instances.

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine(dr["Name"] + "      " + dr["IsLocal"]);
                }
            }

Another approach, how to get local instances is to get it from registry.

RegistryKey rk = Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
      foreach (String element in instances)
      {
            if (element == "MSSQLSERVER")
                  Console.WriteLine(System.Environment.MachineName);
            else
                  Console.WriteLine(System.Environment.MachineName + @"\" + element);
      }
}