Update : 23-01-2011 – Stephen Spence noticed a bug in the script, versions of SQL Server 2000 below SP3 would not be identified.
I’ve updated the script below, it now identifies down to the gold build of SQL Server 2000 but will give “unknown” for some of those older versions as a type.

This powershell script will scan the registry of servers remotely.

$infile = ".\" + $args[0] + ".csv"
$outfile = ".\" + $args[0] + "_out.csv"

$ErrorActionPreference = "SilentlyContinue"
"Server, Instance, Version, Edition" | Out-File $outfile
foreach ($Server in import-csv $infile)
{
  $server
  $version = $null
  $reg = $null
  $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Server.Name)

  $regkey = $null
  $regkey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")

  IF ($regkey){
  foreach ($regInstance in $regkey.GetValueNames())
  {
    $regInstanceData = $regKey.GetValue($regInstance)
    $versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$regInstanceData\\Setup")
    $version = $versionKey.GetValue('PatchLevel')
    $edition = $versionKey.GetValue('Edition')
    $VersionInfo = $Server.Name + ',' + $regInstance + ',' + $version + ',' + $edition
    $versionInfo | Out-File $outfile -Append
  }}

  IF ($version -eq $null)
  {
    $regkey = $null
    $regkey = $reg.OpenSubkey("SOFTWARE\\Microsoft\\Microsoft SQL Server")
    IF ($regkey){
    foreach ($regInstance in $regkey.GetValue('InstalledInstances'))
    {
      IF ($regInstance -eq "MSSQLServer")
      {
	$versionKey = $reg.OpenSubkey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQLSERVER\Setup")
	IF ($versionKey.GetValue('PatchLevel') -eq $null)
	{
          $versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\MSSQLServer\\Setup")
          IF ($versionKey.GetValue('PatchLevel') -eq $null)
          {
            $versionkey = $reg.OpenSubkey("SOFTWARE\\Microsoft\MSSQLServer\\MSSQLServer\\CurrentVersion")
          }
 	}
      }
      ELSE
      {
        $versionKey = $reg.OpenSubkey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$regInstance\\Setup")
      }
      IF ($versionkey)
      {
        $version = $versionKey.GetValue('PatchLevel')
        $edition = $versionKey.GetValue('Edition')
        IF ($version -eq $null)
        {
          $version = $versionKey.GetValue('CSDVersion')
          IF ($version -eq $null)
          {
	    $version = $versionKey.GetValue('CurrentVersion')
	  }
        }
	IF ($Edition -eq $null){$Edition = "Unknown"}
        IF ($version)
        {
          $VersionInfo = $Server.Name + ',' + $regInstance + ',' + $version + ',' + $edition
          $versionInfo | Out-File $outfile -Append
        }
      }
    }}
  }
}

What you need is a list of servers.
It should have a csv extension and the first column should say Name.

Then simply run the script giving the filename without the csv extension as a parameter :

The result will be written to a file with the same name as the input file but with _out.csv :

Do you have a lot of servers, and you want to know how many you need to patch ? :

This relies on the Remote Registry service running on the server you are scanning and you also need to have sufficient rights (local admin). I’m using it myself to scan the boxes that I manage since I’m a local admin on each one. But to get an overall scan done of the entire network I’ve asked a domain admin to run it for me and send me the resulting out_csv.

(I’ve created a list of servers for him to scan using this method)

The script is tested starting with SQL Server 2000 and al the way up to 2008 R2
(2000 stores the info in the registry in a different location from later editions of SQL Server, which is why the script is as long as it is.
However, it did find a frightening amount of SQL Server 2000 servers at my customers site  )