How to list the SQL Server Instances installed on a local machine? ( Only local )
Direct access to Windows Registry isn't the recommended solution by MS, because they can change keys/paths. But I agree that SmoApplication.EnumAvailableSqlServers() and SqlDataSourceEnumerator.Instance fails providing instances on 64-bit platforms.
Getting data from Windows Registry, keep in mind the difference in Registry access between x86 and x64 platforms. 64-bit edition of Windows stores data in different parts of system registry and combines them into views. So using RegistryView is essential.
using Microsoft.Win32;
RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
{
RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
if (instanceKey != null)
{
foreach (var instanceName in instanceKey.GetValueNames())
{
Console.WriteLine(Environment.MachineName + @"\" + instanceName);
}
}
}
If you are looking for 32-bit instances on a 64-bit OS (pretty weird, but possible), you will need to look:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
You could call EnumAvailableSQlServers with a localOnly = True
public static DataTable EnumAvailableSqlServers(bool localOnly)
See MSDN docs for EnumAvailableSqlServers
you can use registry to get sql server instance name in local system
private void LoadRegKey()
{
RegistryKey key = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names");
foreach (string sk in key.GetSubKeyNames())
{
RegistryKey rkey = key.OpenSubKey(sk);
foreach (string s in rkey.GetValueNames())
{
MessageBox.Show("Sql instance name:"+s);
}
}
}
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();
foreach (System.Data.DataRow row in table.Rows)
{
if (row["ServerName"] != DBNull.Value && Environment.MachineName.Equals(row["ServerName"].ToString()))
{
string item = string.Empty;
item = row["ServerName"].ToString();
if(row["InstanceName"] != DBNull.Value || !string.IsNullOrEmpty(Convert.ToString(row["InstanceName"]).Trim()))
{
item += @"\" + Convert.ToString(row["InstanceName"]).Trim();
}
listview1.Items.Add(item);
}
}