Convert ROWS to COLUMNS in Powershell; SQL Server Version and Edition Information from multiple instance.

 

The below query will extract Editions,Version and Service Pack information of all the SQL Server Instances. In the server list array provide SQL Server instance information, if you provide windows server name it might throw error or will skip that paritcular server information.

cls

Import-Module sqlps -DisableNameChecking
#[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo')|Out-Null
$Counter=0
$serverlist = @('SQLServerInstance1','SQLServerInstance2','SQLServerInstance3')
#ForEach-Loop start
$serverlist| ForEach-Object{
#$x = 1
#$perc=[math]::round($x/$serverlist.count)
$indsserver=$_
$Server=New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver
#$Server.Information.Properties|Select-Object Name, Value
$netName = ($Server.Information.Properties| ?{$_.Name -like 'Netname'}).VAlue
$product = ($server.Information.Properties | ?{$_.Name -like 'Product'}).VAlue
$Edition = ($server.Information.Properties | ?{$_.Name -like 'Edition'}).Value
$buildnumber = ($Server.Information.Properties|?{$_.Name -like 'BuildNumber'}).Value
$versionString = ($Server.Information.Properties|?{$_.Name -like 'VersionString'}).Value
$proDuctlvl = ($Server.Information.Properties|?{$_.Name -like 'ProductLevel'}).Value
$isClustered = ($Server.Information.Properties|?{$_.Name -like 'IsClustered'}).Value
#Write-progress -id 1 -percent $perc -activity “Looking up $($netName)”
Write-Progress -Activity "Finding Server $($netName)" -Status "Progress:"  -PercentComplete (($Counter/$serverlist.Count)*100)
$Counter ++
[pscustomobject]@{
NetName=$netName
BuildNumber=$buildnumber
product=$product
Edition=$Edition
VersionString=$versionString
ProductLevel=$proDuctlvl
IsClustered=$isClustered
}
#ForEach-LoopEnds below
}|Export-Csv -Path 'C:\Temp\PatchServerList.csv' -NoTypeInformation