#PSTip Retrieve all SQL instance names on local and remote computers

I was looking at way to retrieve the SQL instance names on a remote computer without using SQL SMO. This is essential for me as I don’t always expect to have the SQL SMO DLLs on the computer where I run my scripts.

We can certainly use the Windows Registry to find this information. If the remote system has a SQL service running, the SQL instance information is stored under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL key. We can use the remote Registry service to query for this information from remote systems. So, having remote Registry service enabled on the target systems is essential for the following script to work.

Function Get-SQLInstance {

    param (
        [string]$ComputerName = $env:COMPUTERNAME,

    try {
        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName)
        $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )
        $instances = $regkey.GetValueNames()

        if ($InstanceName) {
            if ($instances -contains $InstanceName) {
                return $true
            } else {
                return $false
        } else {
    catch {
        Write-Error $_.Exception.Message
        return $false

You can use the above function as follows:

PS C:\> Get-SQLInstance

PS C:\> Get-SQLInstance -ComputerName SQL1

PS C:\> Get-SQLInstance -ComputerName SQL1 -InstanceName SQLServer
Filed in: Columns, Tips and Tricks Tags: , ,

7 Responses to "#PSTip Retrieve all SQL instance names on local and remote computers"

  1. disqus_3yu60JDjck says:

    (get-itemproperty ‘HKLM:SOFTWAREMicrosoftMicrosoft SQL Server’).InstalledInstances

    • Ravikanth says:

      Why not? 🙂 PowerShell has more than one way to do things and you got a one-liner there. But, using the remote registry gives us the ability to talk to remote computers too. That was the idea!

  2. kiquenet kiquenet says:

    It’s great. Any Async powershell about it?

  3. Narasimha says:

    This very cool script when I am passing each server is command line but I would like do few modifications to meet my requirement.

    Step1: Get the list of server name from a T-SQL table using something like below and….

    $Tsql = “SELECT svr_name FROM server”
    $ServerList = Invoke-Sqlcmd -Query $Tsql -ServerInstance $sql_inst -Database $sql_db

    Step2 : Take the in-put of servers names and featch the instance name and put back those instance names to a table.

    So that my table contacts server name and instance names.

Leave a Reply

Submit Comment

© 2018 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: