Get SQL Server Version Information

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 8 Jun 2020

Description

Returns a single row containing a user-friendly description of the current SQL Server Version/Edition

Code

--#region drop if exists

if exists (select 1 from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'udf_sqlversioninfo' and ROUTINE_SCHEMA='dbo' and ROUTINE_TYPE='FUNCTION')

drop function [dbo].[udf_sqlversioninfo];

go

--#endregion-

-- select * from dbo.udf_sqlversioninfo()

--#region create function udf_sqlversioninfo

create function dbo.udf_sqlversioninfo

()

/*

name udf_sqlversioninfo

developer wilfred van dijk (http://www.wilfredvandijk.nl)

returns @productinfo table

purpose Returns SQL Version info in a table

parameters (none)

*/

returns @productinfo table(ProductVersion sysname, ProductValue numeric(18,7), ProductName varchar(16), ProductLevel varchar(8), Major int, Minor int, Build int, BuildVersion int, Edition sysname, EngineEdition varchar(16), LicenseType sysname)

as

begin

insert into @productinfo(ProductVersion, ProductLevel, Major, Minor, Build, BuildVersion, Edition, LicenseType)

select cast(serverproperty('Productversion') as sysname)

, cast(serverproperty('ProductLevel') as sysname)

, parsename(cast(serverproperty('Productversion') as sysname),4)

, parsename(cast(serverproperty('Productversion') as sysname),3)

, parsename(cast(serverproperty('Productversion') as sysname),2)

, parsename(cast(serverproperty('Productversion') as sysname),1)

, cast(serverproperty('Edition') as sysname)

, cast(serverproperty('LicenseType') as sysname)

update @productinfo

set Productvalue = cast(

parsename(cast(serverproperty('Productversion') as sysname),4)

+ '.'

+ parsename(cast(serverproperty('Productversion') as sysname),3)

+ parsename(cast(serverproperty('Productversion') as sysname),2)

+ parsename(cast(serverproperty('Productversion') as sysname),1)

as numeric(18,7));

update @productinfo

set ProductName = 'MSSQL ' + case

when Major = 7 then '7.x'

when Major = 8 then '2000'

when Major = 9 then '2005'

when Major = 10 and Minor = 0 then '2008'

when Major = 10 and Minor = 50 then '2008R2'

when Major = 11 then '2012'

when Major = 12 then '2014'

when Major = 13 then '2016'

when Major = 14 then '2017'

when Major = 15 then '2019'

else cast(Major as varchar) + '.' + cast(Minor as varchar)

end ;

update @productinfo

set EngineEdition = case cast(serverproperty('EngineEdition') as int)

when 1 then 'Personal'

when 2 then 'Standard'

when 3 then 'Enterprise'

when 4 then 'Express'

when 5 then 'Azure'

else 'unknown (' + cast(serverproperty('EngineEdition') as varchar) + ')'

end;

return;

end

go

--#endregion