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

 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)

Quest

Powershell, SQL Scripts, SQL Server
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

10 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
  • Cheryl Stange
    August 5, 2020 2:09 am

    These are great, but I have been trying to find a way to list all databases in all of the instances on one SQL server. We have a number of servers with over 12 SQL instances and many databases that reside on those instances. Is there any way to do this? All I find are ways to get the databases from a single instance.

    Reply
  • Hi Cheryl,
    The Microsoft Assessment and Planning (MAP) Toolkit is very good for this, assuming you have enumeration rights on the databases

    Reply
  • Raphael Ferreira
    October 1, 2020 9:18 pm

    I have the same exact predicament. Did you ever figure it out? Ideally we should get a list with a column for the InstanceName and a column for the database name in the query results. Kindly let me know if you figured it out. THANK you in advance, Raphael.

    Reply
  • Raphael Ferreira
    October 1, 2020 9:33 pm

    Thank you, Zol’taan! That is PERFECT if I do not want to mess with PowerShell and such. Question: Would you known if there is a way to also include the databases inside each instance? So, we would have in the query results one column with the instance name, and another column with the databases belonging to the instance. I hope I explained it clearly enough. TIA, Raphael

    Reply

Leave a Reply