Here I’m sharing a piece of PowerShell code snippet which allow you to create a string array of all your SQL Server with their instances. This code is a .NET namespace and use the enumerator class “SqlDataSourceEnumerator” to give you all available SQL Server instances in your network.
Just go ahead and give it a try:
$SQLInst = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() `
| select @{Expression={if(($_.InstanceName).length -gt 0)`
{$_.ServerName+"\"+$_.InstanceName}else{$_.ServerName}}; Name="SQLName"}
[Array] $MyServers
$MyServers = foreach($i in $SQLInst) {$i.SQLname}
Please, notice I’m using the reverse accent character for line continuation so it’s easy to view. This is a good code snippet, if you need to provide a script solution that may required to get some SQL information from scheduled task on a server that have no SQL engine installed.
Hint: Now, you may have another way to void typing all your server in your PowerShell script.
Also, take a look at this other blog from Thomas LaRock “SQL RockStar” about “Finding Servers on Your Network” – Excellent material – http://thomaslarock.com/2009/12/finding-servers-on-your-network/ (Thanks to Chad Miller! For providing the link)
Here’s another snippet using above Thomas LaRock blog but I use PowerShell to extend the command “sqlcmd /L”:
$s = sqlcmd /L
[array] $Servers = $null
[array] $Servers = foreach($i in $s){if($i.length -gt 0){$i.Trim()}}
$Servers
Happy PowerShelling!!!
🙂