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

 

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 = "SQL";

            string serviceoutput = string.Empty;

            ServiceController[] services = ServiceController.GetServices();

            foreach (ServiceController service in services)

            {

                if (service == null)

                    continue;

                if (service.ServiceName.Contains(servicename))

                {

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

                }

            }

            richTextBox1.Text = serviceoutput;

}

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

Method 2 – Using WMI Query

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 -> http://msdn.microsoft.com/en-us/library/ms179591.aspx

The above methods work for SQL Server 2000/2005/2008 and it can enumerate all SQL services (Database/Reporting/Analysis/Integration/FullText/Browser/Agent/VSS).

Sudarshan Narasimhan,
Technical Lead, Microsoft Sql Server

Microsoft SQL Server Tips & Tricks : HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE