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

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)asbegininsert 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 @productinfoset 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 @productinfoset ProductName = 'MSSQL ' + casewhen 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 @productinfoset 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;endgo