#PSTip Enumerate all SQL Server instances in a network

Note: This tip requires PowerShell 2.0 or above.

Ever thought about how you can list of all SQL Server instances on the local network? SQL Management Objects (SMO) provides a way to do that. We can use the EnumAvailableSqlServers() Method in the SmoApplication class to achieve this.

Let us see how:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

There are a couple of other variants of this method.

We can use EnumAvailableSqlServers($true) to list only the local SQL Server instances. Using $false as the method argument has the same effect as the above method of listing all instances on the network.

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

And, EnumAvailableSqlServers(“MyDBServer”) will return SQL instances on the remote server named MyDBServer.

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