HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Have you ever wondered if there is a programmatic way to detect all the SQL server instances and services installed on a machine. Well, worry no more as the code below will do exactly that. There are 2 ways to go about this :

Method 1 – For the Programmer

The code below is written in C#.

1) Create a new Visual C# Windows Application project.

2) Add a RichTextBox control to your Form1.

3) Add a Button control to your Form1 called GetmeSQL.

4) In the Form1.cs page, add the following code.

//Import the Service namespace

using System.ServiceProcess;

5) Right-click on the Project in “Solution Explorer” -> Add Reference. Choose System.ServiceProcess and say OK.

6) Double-click on GetmeSQL button to take you to the code window and then copy-past the code given below.

private void GetmeSQL_Click(object sender, EventArgs e)

        {

                    string servicename = "MSSQL";

                    string servicename2 = "SQLAgent";

                   string servicename3 = "SQL Server";

                    string servicename4 = "msftesql";

            string serviceoutput = string.Empty;

            ServiceController[] services = ServiceController.GetServices();

            foreach (ServiceController service in services)

            {

                if (service == null)

                    continue;

                                if (service.ServiceName.Contains(servicename) || service.ServiceName.Contains(servicename2) || service.ServiceName.Contains (servicename3) || service.ServiceName.Contains(servicename4))

                   {

                       serviceoutput = serviceoutput + System.Environment.NewLine + "Service Name = " + service.ServiceName + System.Environment.NewLine + "Display Name = " + service.DisplayName + System.Environment.NewLine + "Status = " + service.Status + System.Environment.NewLine;

                   }

            }

            if (serviceoutput == "")

                {

                        serviceoutput += "There are no SQL Server instances present on this machine!" + System.Environment.NewLine;

                }

            richTextBox1.Text = serviceoutput;

       }

 

7) Now build your project and bingo ! Here is how it looks :-

 

 

Method 2

Copy the code given below and save it as Filename.vbs

 strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

Set colItems = objWMIService.ExecQuery( "SELECT * FROM SqlService",,48)

For Each objItem in colItems

    Wscript.Echo "-----------------------------------"

    Wscript.Echo "SqlService instance"

    Wscript.Echo "-----------------------------------"

    Wscript.Echo "DisplayName: " & objItem.DisplayName

    Wscript.Echo "ServiceName: " & objItem.ServiceName

    Wscript.Echo "SQLServiceType: " & objItem.SQLServiceType

Next

To execute above script run it from command prompt using c:\>cscript filename.vbs or just double-click on the script.

The Service Types are documented here -> https://msdn.microsoft.com/en-us/library/ms179591.aspx

Method #1 will work for SQL Server 2000/2005/2008 and it can enumerate all SQL services (Database/Reporting/Analysis/Integration/FullText/Browser/Agent/VSS), whereas Method #2 works only for SQL 2005. It can be tweaked to make use of the namespace - root\Microsoft\SqlServer\ComputerManagement10to get it to work for SQL Server 2008.

Sudarshan Narasimhan,
Technical Lead, Microsoft Sql Server

Comments

• Anonymous
  March 06, 2009
  PingBack from http://www.clickandsolve.com/?p=19123

• Anonymous
  November 17, 2009
  Wouldn't the first method potentially result in false positives? For example, the default service name for the MySQL server is "MySQL", which contains the string "SQL".

• Anonymous
  December 01, 2009
  David, Yes you're right. I am just looking for the keyword "SQL" in the service name. You can modify the code as given below to avoid any false positivies. Now, we will still all SQL services (browser,agent,fulltext) but avoid any non-MSSQL services like MySQL etc. Thanks for bringing this to my attention. // Add these 3 new variables string servicename2 = "SQLAgent"; string servicename3 = "SQL Server"; string servicename4 = "msftesql"; // Replace the IF condition given above with this if (service.ServiceName.Contains(servicename) || service.ServiceName.Contains(servicename2) || service.ServiceName.Contains(servicename3) || service.ServiceName.Contains(servicename4))                    {                        serviceoutput = serviceoutput + System.Environment.NewLine + "Service Name = " + service.ServiceName + System.Environment.NewLine + "Display Name = " + service.DisplayName + System.Environment.NewLine + "Status = " + service.Status + System.Environment.NewLine;                    }

• Anonymous
  January 14, 2010
  string servicename3 = "SQL Server"; does not work for SQL 2005 Express you may use string servicename3 = "MSSQL$" instead to find out the instance of the installed server, but it wont work for 2000.

• Anonymous
  July 21, 2011
  sc query state= all | find "DISPLAY_NAME: SQL" sc query state= all | find "MSSQL" ... Don't use scrips unless it's really necessary. You need fast and easy to remember solutions, ready to use on every host you login. In any case you can use sc tool to get info about remote systems too. sc /? for more info.

• Anonymous
  October 06, 2011
  The comment has been removed

• Anonymous
  October 10, 2011
  The comment has been removed

• Anonymous
  February 13, 2012
  Thanks Very Much

• Anonymous
  August 17, 2015
  The comment has been removed

• Anonymous
  September 24, 2015
  Is there any situation that the sql server is installed but the related service is not running at all?