QuickBlog: Build a list of SQL Server without SMO…

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!!!

🙂

Technorati Tags: ,