2

On a SQL Server 2012 instance, how can I get the names of SQL Server instances using a SQL query?

I want to show instance names currently installed in SQL Server.

  • 1
    Do you want to know this for every server you have in your domain? Or just on one server ? – Stijn Wynants Mar 31 '16 at 10:30
7

Here are some options using what is already available on a server with Windows Server and SQL Server installed.

You can list Services on a server with a name starting with SQL Server (...:

sc \\IP_or_Name query | find /I "SQL Server ("

IP_or_Name is the IP or DNS name of the Windows Server 20xx server.

Or with Powershell:

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

You can look at the content of the registry key HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL using Regedit.

You can also use Powershell:

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

Key HKLM:\Software\Microsoft\Microsoft SQL Server\InstalledInstances gives a list of name separated by line breaks.


Powershell commands can be executed remotely using Invoke-Command:

Invoke-Command -ComputerName computer -ScriptBlock { command } -Credential username

Using sqlcmd you can get a list of the instances which broadcast their names on the network:

sqlcmd -L

Or with Powershell which has the advantage of showing Version number:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
3
  1. Open a Remote Desktop session to the server where the SQL instances are installed.
  2. Launch 'Start Menu' -> 'Microsoft SQL Server' -> 'SQL Server Installation Center'
  3. Select the 'Tools' tab from the left side.
  4. Click 'Installed SQL Server Features Discovery Report'

Not the answer you're looking for? Browse other questions tagged or ask your own question.