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.


Index: PowerShell HowTo’s


Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s