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.