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) 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




ċ
Andy Hughes,
Jun 8, 2020, 7:42 AM
Comments