How To List SQL Server Instances In PowerShell

How To List SQL Server Instances In PowerShell

How? As easy as running the code below.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Format-Table -Auto

And you’ll be presented with something like this (shown names are fake, of course):

ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------
SERVER01                No          10.50.1600.1
SERVER02   INSTANCE01   No          11.0.3000.0
SERVER03   INSTANCE02   No          11.0.3000.0

Which is a regular PowerShell object, so you’re free to mangle it at will.

If this doesn’t work for you, you can try using SMO:

$Current = Get-Location;
Import-Module SQLPS -DisableNameChecking;
Set-Location $Current;

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

And this still doesn’t work for you, or if you’re trying to query a computer not on a network connection, you can try WMI too:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null;

$MC = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer("COMPUTER_NAME_HERE");
foreach ($Instance in ($MC.Services | Where-Object { $_.Type -Eq "SqlServer" }))
{
    Write-Host $Instance.Name;
}

The WMI option has the advantage of listing other services too if you need, like SSAS, SSRS, SQL Agent, etc.

Jorge Candeias's Picture

About Jorge Candeias

Jorge helps organizations succeed by building high-performing solutions on the Microsoft tech stack.

London, United Kingdom https://jorgecandeias.github.io