Detect local SQL server installation with C#(32 bit as well as 64 bit)

Question

How can I detect if SQL is installed on the local machine using C#? Is it possible to check a remote machine?

Answer 1

You could use the System.Management namespace to check for the existence of SQL Server in the server's running services.

Answer 2

You can check registry path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances

For an example of code that does similar work, see this quesion how-to-get-sql-server-installation-path-programatically

Please also see MSDN: File Locations for Default and Named Instances of SQL Server for more details on registry keys used by SQL Server.

Answer 3

We check the the registry

Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion", ", "0.0.0.0");

Answer 4

You will need SQL 2005 Backwards Compatibility redist. See: http://stackoverflow.com/questions/1135636/how-to-connect-to-sqlserver2008-using-smo-any-workaround-has-to-be-done/1135683#1135683

using Microsoft.SqlServer.Management.Smo;

DataTable dt = SmoApplication.EnumAvailableSqlServers(true);
         string[] szSQLInstanceNames = new string[dt.Rows.Count];
         StringBuilder szSQLData = new StringBuilder();

         if (dt.Rows.Count > 0)
         {
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
               try
               {
                  szSQLInstanceNames[i] = dr["Name"].ToString();
                  Server oServer;
                  oServer = new Server(szSQLInstanceNames[i]);
                  if (string.IsNullOrEmpty(dr["Instance"].ToString()))
                  {
                     szSQLInstanceNames[i] = szSQLInstanceNames[i] + "\\MSSQLSERVER";
                  }
                  szSQLData.AppendLine(szSQLInstanceNames[i] + "  Version: " + oServer.Information.Version.Major + "  Service Pack: " + oServer.Information.ProductLevel + "  Edition: " + oServer.Information.Edition + "  Collation: " + oServer.Information.Collation);
               }
               catch (Exception Ex)
               {
                  szSQLData.AppendLine("Exception occured while connecting to " + szSQLInstanceNames[i] + " " + Ex.Message);
               }

               i++;
            }

Note: if you just want to see if Default intance is installed or no just do:

Server oServer; oServer = new Server(Environment.MAchineName);

if it does not throw an exception, the SQL exists.

Answer 5

You could just open a connection to the machine and close it. If you throw an exception that's a decent sign. I realize it's not super clean but it'll get the job done.

Answer 6

Perhaps you'll find the following useful. Use first method to find about servers (local & network), then you can use the second to enumerate databases on each server.

using System;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;

namespace Info.Data.Engine.SQLServer
{
  public static class SQLServerHelper
  {
    public static List<String> EnumerateServers()
    {
      var instances = SqlDataSourceEnumerator.Instance.GetDataSources();
      if ((instances == null) || (instances.Rows.Count < 1)) return null;

      var result = new List<String>();
      foreach (DataRow instance in instances.Rows)
      {
        var serverName = instance["ServerName"].ToString();
        var instanceName = instance["InstanceName"].ToString();
        result.Add(String.IsNullOrEmpty(instanceName) ? serverName : String.Format(@"{0}\{1}", serverName, instanceName));
      }
      return result;
    }

    public static List<String> EnumerateDatabases(String connectionString)
    {
      try
      {
        using (var connection = new SqlConnection(connectionString))
        {
          connection.Open();
          var databases = connection.GetSchema("Databases");
          connection.Close();
          if ((databases == null) || (databases.Rows.Count < 1)) return null;

          var result = new List<String>();
          foreach (DataRow database in databases.Rows)
          {
            result.Add(database["database_name"].ToString());
          }
          return result;
        }
      }
      catch
      {
        return null;
      }
    }
  }
}

HTH, Dejan