List Out All the SQL servers In domain



In One Of the Group the question was asked that How to list all the Servers in which SQL is installed?

It is genuine query. There are many scenario when you need to Find out how much SQL servers are there in the domain ? many a times if a environment is not properly managed and someone takes charge as DBA first thing asked is how much servers we have ?

On The Internet we will find the many tools to do so. Microsoft uses their tool MAP to  discover the servers it is perfect tool and easy to use but many time third party tool is not allowed in those scenario  Power-shell comes to rescue . Powershell  script perfect solution to find all the SQL servers in domain.I searched on the net for that as i am not a developer and powershell is not my forte . i find the below script .

You need to change the domain here . replace corporate with your domain.

once you run this script in powershell , on the screen you will get the list of all the servers in the  domain and the result of ping status . but actual thing you will find in the location  “C:\Users\username” . you will get the excel file named as Yourdomain_Servers_out  this file will list out all the discovered  sql server with details of Server, Instance, Version, Edition .



      [string]$domain = “corporate”


## ==================================================================================

## Title       : Find All Servers in a Domain With SQL

## Description : Get a listing of all servers in a domain, test the connection

##               then check the registry for MS SQL Server Info.

##                 Output(ServerName, InstanceName, Version and Edition).

##                 Assumes that instances of MS SQL Server can be found under:

##                 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

## Author      : C.Perry

## Date        : 10/2/2012

## Input       : -domain <fully.qualified.domainname>    

## Output      : List of SQL Server names

## Usage       : PS> . FindAllServersWithSQL.ps1 -domain

## Notes       :

## Tag           : SQL Server, test-connection, ping, AD, WMI

## Change log  :

## ==================================================================================



# Domain context

#$domain = $null


# Initialize variables and files

$dom = $null

$ErrorActionPreference = “Continue”

$found = $null

$InstNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

$RegInstNameKey = $null

$MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

$notfound = $null

#Output file goes into directory you execute from

$outfile = “$domain” + “_Servers_out.csv” 

$reg = $null

$regInstance = $null

$regInstanceData = $null

$regKey = $null

$root = $null

$SetupVersionKey = $null

$SQLServerkey = $null

$sbky = $null

$sub = $null

$type = [Microsoft.Win32.RegistryHive]::LocalMachine

“Server, Instance, Version, Edition” | Out-File $outfile

# Domain Initalization

# create the domain context object

$context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext(“domain”,$domain)

# get the domain object

$dom = [system.directoryservices.activedirectory.domain]::GetDomain($context)

# Debug line #$dom 

# go to the root of the Domain

$root = $dom.GetDirectoryEntry()

#create the AD Directory Searcher object

$searcher = new-object System.DirectoryServices.DirectorySearcher($root)

#filter for all servers that do not start with “wde”

$filter=”(&(objectClass=Computer)(operatingSystem=Windows Server*) (!cn=wde*))”

$searcher.filter = $filter

# By default, an Active Directory search returns only 1000 items.

# If your domain includes 1001 items, then that last item will not be returned.

# The way to get around that issue is to assign a value to the PageSize property. 

# When you do that, your search script will return (in this case) the first 1,000 items, 

# pause for a split second, then return the next 1,000. 

# This process will continue until all the items meeting the search criteria have been returned.


$colProplist = “name”

foreach ($j in $colPropList){$searcher.PropertiesToLoad.Add($j)}

# get all matching computers

$colResults = $searcher.FindAll()

# PROCESS Section

# interate through all found servers

foreach ($objResult in $colResults)

{#Begin ForEach

    $objItem = $objResult.Properties




        IF (test-connection -computername $Server  -count 1 -TimeToLive 4 -erroraction continue -quiet)


            $found = $Server + ” is pingable”

            #echo $found

            $InstanceNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

            $MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

            $type = [Microsoft.Win32.RegistryHive]::LocalMachine

            $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $Server)

            $SQLServerkey = $null

            $SQLServerkey = $regKey.OpenSubKey($MSSQLkey)

            # Check to see if MS SQL Server is installed

            IF ($SQLServerkey)

            {#Begin IF $SQLSERVERKEY    

                #DEBUG Write to Host “Sub Keys”


                #Write-Host “Sub Keys for $MSSQLkey”

                #Write-Host “——–“

                #Foreach($sbky in $SQLServerkey.GetSubKeyNames()){$sbky}

                $Instkey = $null

                $Instkey = $regKey.OpenSubKey($InstanceNameskey)

                # Check to see in chargeable Instances of MS SQL Server are installed

                IF ($Instkey)


                    #DEBUG Write-Host “Values” of SubKeys


                    #Write-Host “Sub Keys for $InstanceNameskey”

                    #Write-Host “——“

                    #Foreach($sub in $Instkey.GetSubKeyNames()){$sub}

                    foreach ($regInstance in $Instkey.GetSubKeyNames())  


                        $RegInstNameKey = $null

                        $SetupKey = $null

                        $SetupKey = “$InstanceNameskey\$regInstance”

                        $RegInstNameKey = $regKey.OpenSubKey($SetupKey)

                        #Open Instance Names Key and get all SQL Instances

                        foreach ($SetupInstance in $RegInstNameKey.GetValueNames())  


                            $version = $null  

                            $edition = $null

                            $regInstanceData = $null

                            $SetupVersionKey = $null

                            $VersionInfo = $null

                            $versionKey = $null

                            $regInstanceData = $RegInstNameKey.GetValue($SetupInstance) 

                            $SetupVersionKey = “$MSSQLkey\$regInstanceData\Setup”

                            #Open the SQL Instance Setup Key and get the version and edition

                            $versionKey = $regKey.OpenSubKey($SetupVersionKey)

                            $version = $versionKey.GetValue(‘PatchLevel’) 

                            $edition = $versionKey.GetValue(‘Edition’)   

                            # Write the version and edition info to output file

                            $VersionInfo = $Server + ‘,’ + $regInstanceData + ‘,’ + $version + ‘,’ + $edition  

                            $versionInfo | Out-File $outfile -Append 

                        }#end foreach $SetupInstance

                    }#end foreach $regInstance

                }#end If $instKey


                {#Begin No Instance Found

                    $found = $found + ” but no chargable instance found.”

                    echo $found

                }#End No Instance Found

            }#end If $SQLServerKey

        }#end If Connectionfound


        {#ELSE Connection Not Found

            $notfound = $Server + ” not pingable”

            echo $notfound





         $exceptionType = $_.Exception.GetType()

        if ($exceptionType -match ‘System.Management.Automation.MethodInvocation’)


             #Attempt to access an non existant computer

             $Wha=$Server + ” – ” +$_.Exception.Message

               write-host  -backgroundcolor red  -foregroundcolor Black $Wha   


        if ($exceptionType -match ‘System.UnauthorizedAccessException’)


            $UnauthorizedExceptionType = $Server + ” Access denied – insufficent privileges”

            # write-host “Exception: $exceptionType”

            write-host -backgroundcolor red “UnauthorizedException: $UnauthorizedExceptionType”


        if ($exceptionType -match ‘System.Management.Automation.RuntimeException’)


               # Attempt to access an non existant array, output is suppressed

               write-host  -backgroundcolor cyan  -foregroundcolor black “$Server – A runtime exception occured: ”   $_.Exception.Message; 


    }#end Catch

}#end ForEach servers in domain

#number of servers


enjoy the script and comments are always welcome