Microsoft provides a list of Latest updates for Microsoft SQL Server which gives you the latest Cumulative Update (CU) number for each Service Pack (SP) of each version of SQL Server since 2000.
But how do you check which version of SQL Server you have installed on your machines?
Sure, there internets are full of SQL scripts which retrieve this information (MS, SO, TN, etc), but those scripts require you to connect to every SQL Server instance and query it individually.
If you have direct access to the machine running SQL Server (such as your development machine), wouldn’t it be nice if you saw the list of installed SQL Server versions, whether the instances are running or stopped?
So I typed along in the PowerShell ISE, retrieving all executables named sqlservr.exe (the executable hosting SQL Server), retrieving their VersionInfo, and outputting the relevant information:
$basedir = "C:\Program Files\Microsoft SQL Server"
$exes = Get-ChildItem -Path $basedir -Filter sqlservr.exe -Recurse -ErrorAction SilentlyContinue -Force
$exes | foreach-object -Process {
$ip = Get-ItemProperty -Path $_.FullName
$vi = $ip.VersionInfo
[pscustomobject]@{ ProductVersion = $vi.ProductVersion;
FileVersion = $vi.FileVersion;
DirectoryName = $_.DirectoryName;
LastWriteTime = $_.LastWriteTime; }
} | format-table
So, as a first step, I have the ProductVersion numbers.
As I found while researching this script, Microsoft also provides a list of product versions (“build versions”) indicating SQL Server version, Service Pack and Cumulative Update. The list can be downloaded as .xlsx from aka.ms/SQLServerbuilds.
So I have the installed product versions as list of PS objects, and the build numbers in .xlsx format, let’s combine both.
Fortunately, there is a PS library to read .xlsx files called ImportExcel (GitHub), and you install it by simply running
Install-Module ImportExcel -Scope CurrentUser
(you probably need to update PowerShell’s nuget, which is done semi-automatically in the process)
As it turned out, ImportExcel is PS code wrapping OfficeOpenXml.ExcelPackage, which I have dealt with in previous C# projects, so you do not have to have Excel installed when parsing the downloaded .xlsx.
The script uses Get-ExcelSheetInfo to query all the worksheets of the Excel file, and for each worksheet runs Import-Excel to retrieve the worksheet’s data into a hashtable indexed by the build number.
Finally, the original procedure is extended to lookup the ProductVersion number in the hashtable, and merge the Excel data with the original result:
Both versions of the script are available on my GitHub PowerShell repository.
devioblog 