SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine?

There are multiple ways by which we can find a list of SQL Server instances name instances on a machine. In this blog, I would show a few ways to do it.

Command Prompt

Based on my research, registry key would have an entry whenever SQL is installed. So we can query the registry key to find details about instance name.

 reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

SQL SERVER - How to Get List of SQL Server Instances Installed on a Machine? get-inst-02

If we want to query services, then we can search for all services and look for display name

Solarwinds
 search query | findstr "DISPLAY_NAME"|findstr /C:"SQL Server ("

The above method of registry, can’t tell the version.

PowerShell

Whatever we did on method 1, same can be achieved using PowerShell also. Below is the method where we query the registry.

Get-ItemProperty ‘HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’

SQL SERVER - How to Get List of SQL Server Instances Installed on a Machine? get-inst-01

If we want to query services to get then another option would be to use below PowerShell

 Get-Service | ?{ $_.DisplayName -like "SQL Server (*" }

Do you know any more ways? Please share with others via comment.

Here is my earlier article on the same subject:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
Personal Technology – Expanding to Open Folder Working with Windows 10
Next Post
SQL SERVER – FIX: Incorrect Syntax Near the Keyword ‘PROCEDURE’

Related Posts

6 Comments. Leave new

  • I think your meant to say ‘sc query’ vs ‘search query’

    Reply
  • Here is what I use:

    # import SQLPS if necessary
    if((get-module|Where-Object {$_.Name -like “sqlps”}|Measure-Object).Count -eq 0){Import-Module sqlps};

    Get-ChildItem SQLServer:\SQL\$server_name;

    Reply
  • Pulling SQL Server instances from the registry is a little cleaner if you use:

    (Get-ItemProperty ‘HKLM:\Software\Microsoft\Microsoft SQL Server\’).InstalledInstances

    Getting instance names from Get-Service and just return the instance name:

    (Get-Service -DisplayName “SQL Server (*”).DisplayName -replace ‘.*\((.*)\).*’, ‘$1’

    Reply
  • Vincent Duvernet
    December 13, 2017 10:35 pm

    It seems it doesn’t return (LocalDB) instances

    Reply
  • – T-SQL Query to find list of Instances Installed on a machine

    DECLARE @GetInstances TABLE
    ( Value nvarchar(100),
    InstanceNames nvarchar(100),
    Data nvarchar(100))

    Insert into @GetInstances
    EXECUTE xp_regread
    @rootkey = ‘HKEY_LOCAL_MACHINE’,
    @key = ‘SOFTWARE\Microsoft\Microsoft SQL Server’,
    @value_name = ‘InstalledInstances’

    Select InstanceNames from @GetInstances

    Reply

Leave a Reply

Menu