With this script, you will be able to get a status on your Microsoft SQL Server version and patch level. This script uses a csv table I have created and that contains the version and patch level for each SQL build version. The csv file is available here and have to copied in the current script location : sql_version_build

The versions listed in the csv file are the following:

• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008R2
• SQL Server 2008
• SQL Server 2005

$host_array = "SQLSRV01",
"SQLSRV02",
"SQLSRV03"

function Get_SQL_info($computername){
	$array_tmp = @()
	$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computername )
	$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL",$true)
	$instancenames = $regkey.GetValueNames()
	$instancenames | % {
		$instance = $_
		$regvalue = $regKey.GetValue($instance)
		$regKeyVersion = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$regvalue\\MSSQLServer\\CurrentVersion",$true)
		$CurrentVersion = $regKeyVersion.GetValue("CurrentVersion")
		$instance_sql_info = $sql_build_info -match $CurrentVersion
		$sql_build = $instance_sql_info.build
		$sql_version = $instance_sql_info.version
		$sql_sp = $instance_sql_info.sp
		$sql_update = $instance_sql_info.update
		$Properties = @{server=$computername;
				instance=$instance;
				sql_build=$sql_build;
				sql_version=$sql_version;
				sql_sp=$sql_sp;
				sql_update=$sql_update}
		$Newobject = New-Object PSObject -Property $Properties
		$array_tmp += $newobject
	}
	return $array_tmp
}

$sql_build_info = import-csv -Delimiter ";" sql_version_build.csv

$sql_array = @()
$host_array | % {
	$sqlservername = $_
	$sql_array += Get_SQL_info($sqlservername)
}
$sql_array

The script output is an array with the following properties :

• server
• instance
• sql_build
• sql_version
• sql_sp
• sql_update

---