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