SQL Server version information from build numbers
Just a quick post in case anyone else finds this useful; for a report I’ve created, I wanted to have SQL Server versions displayed in a nice, readable form, rather than the usual build number format (e.g. 11.0.3128.0).
So I found the very useful SQL Server Builds page (thanks to whoever maintains that), and proceeded to write a function which accepts the build number, and spits out a friendlier “version string”, down to the service pack and cumulative update level of resolution. (I didn’t need anything lower than that, but you could easily add it in yourself).
Here’s the full function — feel free to use, hack to pieces, etc. ;-)
Click to expand the full function (covers SQL 2005 through 2012, including all CU-level build numbers)
IF OBJECT_ID('dbo.fn_sql_version_from_build') IS NULL
EXEC('CREATE FUNCTION dbo.fn_sql_version_from_build (@i INT) RETURNS INT AS BEGIN RETURN(1) END;');
GO
/***********************************************************
Author : David Curlewis
Date : 2013-04-24
Desc. : Accepts a SQL Server build number and returns
a human-readable version string, including
major version (e.g. 2005, 2008, etc), as well
as a pretty close approximation of minor
version (e.g. down to the service pack and
cumulative update level). I've specifically
not included fixes, CTPs, and other more minor
build numbers in order to keep it relatively
simple. All information for this was grabbed
from http://sqlserverbuilds.blogspot.co.nz/,
so many thanks to whoever maintains that. :)
***********************************************************/
ALTER FUNCTION dbo.fn_sql_version_from_build (
@sql_version VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN (
CASE
---------------------------------------------------------------------------------------------------
-- 2005
---------------------------------------------------------------------------------------------------
WHEN @sql_version LIKE '9.%'
THEN CASE
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5266 THEN '2005 SP4 CU3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5259 THEN '2005 SP4 CU2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5254 THEN '2005 SP4 CU1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5000 THEN '2005 SP4'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4325 THEN '2005 SP3 CU15'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4317 THEN '2005 SP3 CU14'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4315 THEN '2005 SP3 CU13'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4311 THEN '2005 SP3 CU12'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4309 THEN '2005 SP3 CU11'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4305 THEN '2005 SP3 CU10'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4294 THEN '2005 SP3 CU9'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4285 THEN '2005 SP3 CU8'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4273 THEN '2005 SP3 CU7'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4266 THEN '2005 SP3 CU6'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4230 THEN '2005 SP3 CU5'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4226 THEN '2005 SP3 CU4'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4220 THEN '2005 SP3 CU3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4211 THEN '2005 SP3 CU2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4207 THEN '2005 SP3 CU1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4035 THEN '2005 SP3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3356 THEN '2005 SP2 CU17'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3355 THEN '2005 SP2 CU16'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3330 THEN '2005 SP2 CU15'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3328 THEN '2005 SP2 CU14'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3325 THEN '2005 SP2 CU13'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3315 THEN '2005 SP2 CU12'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3301 THEN '2005 SP2 CU11'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3294 THEN '2005 SP2 CU10'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3282 THEN '2005 SP2 CU9'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3257 THEN '2005 SP2 CU8'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3239 THEN '2005 SP2 CU7'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3228 THEN '2005 SP2 CU6'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3215 THEN '2005 SP2 CU5'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3200 THEN '2005 SP2 CU4'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3186 THEN '2005 SP2 CU3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3175 THEN '2005 SP2 CU2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3161 THEN '2005 SP2 CU1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3042 THEN '2005 SP2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2047 THEN '2005 SP1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 1399 THEN '2005 RTM'
-- In between builds
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 5266 THEN '2005 SP4 CU3+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 5000 THEN '2005 SP4+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 4035 THEN '2005 SP3+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 3042 THEN '2005 SP2+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 2047 THEN '2005 SP1+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 1399 THEN '2005 RTM+'
ELSE '2005 ?'
END
---------------------------------------------------------------------------------------------------
-- 2008 R2
---------------------------------------------------------------------------------------------------
WHEN @sql_version LIKE '10.50.%'
THEN CASE
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4279 THEN '2008 R2 SP2 CU6'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4276 THEN '2008 R2 SP2 CU5'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4270 THEN '2008 R2 SP2 CU4'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4266 THEN '2008 R2 SP2 CU3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4263 THEN '2008 R2 SP2 CU2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4260 THEN '2008 R2 SP2 CU1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4000 THEN '2008 R2 SP2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2874 THEN '2008 R2 SP1 CU12'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2500 THEN '2008 R2 SP1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 1600 THEN '2008 R2 RTM'
-- In between builds
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 4279 THEN '2008 R2 SP2 CU6+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 4000 THEN '2008 R2 SP2+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 2500 THEN '2008 R2 SP1+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 1600 THEN '2008 R2 RTM+'
ELSE '2008 R2 ?'
END
---------------------------------------------------------------------------------------------------
-- 2008
---------------------------------------------------------------------------------------------------
WHEN @sql_version LIKE '10.0%'
THEN CASE
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5835 THEN '2008 SP3 CU10'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 5500 THEN '2008 SP3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4333 THEN '2008 SP2 CU11'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 4000 THEN '2008 SP2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2850 THEN '2008 SP1 CU16'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2531 THEN '2008 SP1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 1600 THEN '2008 RTM'
-- In between builds
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 5835 THEN '2008 SP3 CU10+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 5500 THEN '2008 SP3+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 4000 THEN '2008 SP2+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 2531 THEN '2008 SP1+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 1600 THEN '2008 RTM+'
ELSE '2008 ?'
END
---------------------------------------------------------------------------------------------------
-- 2012
---------------------------------------------------------------------------------------------------
WHEN @sql_version LIKE '11.%'
THEN CASE
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3349 THEN '2012 SP1 CU3'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3339 THEN '2012 SP1 CU2'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3321 THEN '2012 SP1 CU1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 3000 THEN '2012 SP1'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2405 THEN '2012 RTM CU7'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) = 2100 THEN '2012 RTM'
-- In between builds
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 3349 THEN '2012 SP1 CU3+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 3000 THEN '2012 SP1+'
WHEN CAST(PARSENAME(@sql_version, 2) AS INT) > 2100 THEN '2012 RTM+'
ELSE '2012 ?'
END
ELSE '?'
END);
END
GO
Cheers, Dave