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()
|improve this answer|||||
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'
|improve this answer|||||

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