One of the frustrations with SQL Server is that they do not expose the SQL Server Marketing Name that contains the year in the name as a Server Property. Instead, to get that you either need your own lookup table, or you have to parse it from the @@Version string.
The following query returns every attribute that most people would want in discrete columns:
SELECT @@SERVERNAME as ServerName
, SUBSTRING(@@Version, 11, CHARINDEX(‘ (‘, @@VERSION, 1) – 11) as SQLVersionLabel
, SUBSTRING(@@Version, 22, CHARINDEX(‘ (‘, @@VERSION, 1) – 22) as SQLYearReleaseLabel
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘ProductUpdateLevel’) AS ProductUpdateLevel
, SERVERPROPERTY(‘ProductBuildType’) AS ProductBuildType
, SERVERPROPERTY(‘ProductUpdateReference’) AS ProductUpdateReference
, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion
, SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion
, SERVERPROPERTY(‘ProductBuild’) AS ProductBuild
Combining a query like that with a product like Toad for SQL Server’s group execute feature, it becomes possible to pull the SQL Version info from an entire group of servers all at once.
Darren Myher's Blog 