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 file, feel free to download, use, hack to pieces, etc. 😉
Cheers,
Dave
Nice function. I use a different method that doesn’t provide the CU level but is not relevant in my case.
IF OBJECT_ID(‘dbo.sp_Get_SQL_Version_Text’) IS NOT NULL
DROP PROCEDURE dbo.sp_Get_SQL_Version_Text;
GO
/*————————————————-
Who : Matthew Hill
When : 2012-05-01
What : Outputs SQL version and SP version e.g. 2008 R2 SP1
————————————————-*/
CREATE PROCEDURE dbo.sp_Get_SQL_Version_Text
AS
BEGIN
declare @ver nvarchar(32)
set @ver = cast((select SERVERPROPERTY(‘productversion’)) as nvarchar)
SELECT
case
when @ver like ‘9.%’ then ‘2005’
when @ver like ‘10.0.%’ then ‘2008’
when @ver like ’10.[^0]%’ then ‘2008 R2′
when @ver like ’11.%’ then ‘2012’
when @ver like ’11.[^0]%’ then ‘2012 R2’
END + ‘ ‘ + CAST(SERVERPROPERTY (‘productlevel’) as nvarchar(20)) + ‘ (‘ + @ver + ‘)’
END