Correlate SQL Server version # to name

Question

I have a PowerShell script that incorporates a bunch of different information on servers and workstations to pass off to upper management. As we know, U.M. likes to have everything plain and simple.

What I am trying to do is this... I am using the following script that I found on another S.O. topic that does exactly what I need it to. Pulls up instance names & SQL Server edition / version information.

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

foreach ($i in $inst)
{
     $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
     (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version
     (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition
     $i
}

The output shows:

12.3.6024.0
Standard Edition
MSSQLSERVER

What I can't seem to figure out, and I have been looking at scripts on here all morning long, is how to get it to show something like:

SQL Server 2014
Standard Edition
MSSQLSERVER

I would like it to report if the version # is 12.x it results SQL Server 2014, 13.x it results SQL Server 2017, etc. etc.

How in the bloody world can I get this done? I am learning PowerShell, but when it comes to switches and all that, I am still a bit behind.

Thanks for the assistance ahead of time.

user_2878438 · Answer

You just need a switch (with a regex lookup) added to handle the "mapping" between version and "display" version (as you want it):

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

foreach ($i in $inst)
{
     $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
     $v = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version
     $e = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition

     $mv = switch -Regex ($v) {
        '^8' { 'SQL Server 2000'; Break}
        '^9' { 'SQL Server 2005'; Break }
        '^10.0' { 'SQL Server 2008'; Break }
        '^10.5' { 'SQL Server 2008 R2'; Break }
        '^11' { 'SQL Server 2012'; Break }
        '^12' { 'SQL Server 2014'; Break }
        '^13' { 'SQL Server 2016'; Break }    
        '^14' { 'SQL Server 2017'; Break } 
        '^15' { 'SQL Server 2019'; Break } 
        default { "Unknown SQL Server Version" }
    }

    Write-Host $mv
    write-Host $e
    write-Host $p
}

Correlate SQL Server version # to name

1 Answers