Pulled together some scripts to make a cmdlet which returns version information from one or more SQL servers. Remember to set the SQLServer parameter value correctly. It will be different depending on whether you are using a named instance. You can find a copy of the source code at my powershell.amsterdam gitlab repository.
I’ve used Invoke-Command in the script so that the function can also be run remotely against other systems.
function Get-SQLVersion { [CmdletBinding()] [OutputType([psobject])] param ( [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Computer name')] [string] $Computername = $env:computername, [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Instance')] [string] $SQLServer ) Process { Invoke-Command -ComputerName $Computername -ScriptBlock { $SQLquery = @' SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') '@ $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$using:SQLServer;Database=Master;Integrated Security=SSPI;" $SqlCmd = New-Object -TypeName System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SQLquery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object -TypeName System.Data.DataSet $nSet = $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $Tables = $DataSet.Tables $arrayVersion = ($($Tables.Column1).Split('.')) [string] $SQLVersionNumber = "$($arrayVersion[0]).$($arrayVersion[1])" Switch -Wildcard ($SQLVersionNumber) { '13.0*' { $versionName = 'SQL Server 2016' } '12.0*' { $versionName = 'SQL Server 2014' } '11.0*' { $versionName = 'SQL Server 2012' } '10.50*' { $versionName = 'SQL Server 2008 R2' } '10.0*' { $versionName = 'SQL Server 2008' } '9.0*' { $versionName = 'SQL Server 2005' } '8.0*' { $versionName = 'SQL Server 2000' } '7.0*' { $versionName = 'SQL Server' } } $hash = @{ DisplayName = "$versionName $($Tables.Column2) $($Tables.Column3) $($Tables.Column1)" Name = $versionName Version = $Tables.Column3 ServicePack = $Tables.Column2 VersionNumber = $Tables.Column1 } New-Object -TypeName PSobject -Property $hash } } }
With this complete, we can use the cmdlet, and expect output similar to the format below.