All OS version info in one function

if exists (select * from information_schema.routines where ROUTINE_NAME = 'udf_windows_version' and ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'dbo')
drop function dbo.udf_windows_version
go

create function dbo.udf_windows_version()
returns table
as
return (
with CTE as (
selectwindows_release
, case windows_release
when '6.3' then 'Windows Server 2012 R2'
when '6.2' then 'Windows Server 2012' 
when '6.1' then 'Windows Server 2008 R2'
when '6.0' then 'Windows Server 2008'
when '5.2' then 'Windows Server 2003 / 2003 R2'
when '5.1' then 'Windows XP'
when '5.0' then 'Windows 2000'
else windows_release end as windows_server_release_name
, case windows_release
when '6.3' then 'Windows 8.1'
when '6.2' then 'Windows 8' 
when '6.1' then 'Windows 7'
when '6.0' then 'Windows Vista'
when '5.2' then 'Windows XP 64-bit edition'
when '5.1' then 'Windows XP'
when '5.0' then 'Windows 2000'
else windows_release end as windows_home_release_name
, windows_service_pack_level
, windows_sku
, case windows_sku
when 0x00000006 then 'Business' 
when 0x00000010 then 'Business N' 
when 0x00000012 then 'HPC Edition' 
when 0x00000040 then 'Server Hyper Core V' 
when 0x00000065 then 'Windows 8' 
when 0x00000062 then 'Windows 8 N' 
when 0x00000063 then 'Windows 8 China' 
when 0x00000064 then 'Windows 8 Single Language'
when 0x00000050 then 'Server Datacenter (evaluation installation)' 
when 0x00000008 then 'Server Datacenter (full installation)' 
when 0x0000000C then 'Server Datacenter (core installation)' 
when 0x00000027 then 'Server Datacenter without Hyper-V (core installation)' 
when 0x00000025 then 'Server Datacenter without Hyper-V (full installation)' 
when 0x00000004 then 'Enterprise' 
when 0x00000046 then 'Not supported' 
when 0x00000054 then 'Enterprise N (evaluation installation)' 
when 0x0000001B then 'Enterprise N' 
when 0x00000048 then 'Server Enterprise (evaluation installation)' 
when 0x0000000A then 'Server Enterprise (full installation)' 
when 0x0000000E then 'Server Enterprise (core installation)' 
when 0x00000029 then 'Server Enterprise without Hyper-V (core installation)' 
when 0x0000000F then 'Server Enterprise for Itanium-based Systems' 
when 0x00000026 then 'Server Enterprise without Hyper-V (full installation)' 
when 0x0000003B then 'Windows Essential Server Solution Management' 
when 0x0000003C then 'Windows Essential Server Solution Additional' 
when 0x0000003D then 'Windows Essential Server Solution Management SVC' 
when 0x0000003E then 'Windows Essential Server Solution Additional SVC' 
when 0x00000002 then 'Home Basic' 
when 0x00000043 then 'Not supported' 
when 0x00000005 then 'Home Basic N' 
when 0x00000003 then 'Home Premium' 
when 0x00000044 then 'Not Supported' 
when 0x0000001A then 'Home Premium N' 
when 0x00000022 then 'Windows Home Server 2011' 
when 0x00000013 then 'Windows Storage Server 2008 R2 Essentials' 
when 0x0000002A then 'Microsoft Hyper-V Server' 
when 0x0000001E then 'Windows Essential Business Server Management Server' 
when 0x00000020 then 'Windows Essential Business Server Messaging Server' 
when 0x0000001F then 'Windows Essential Business Server Security Server' 
when 0x0000004C then 'Windows MultiPoint Server Standard (full installation)' 
when 0x0000004D then 'Windows MultiPoint Server Premium (full installation)' 
when 0x00000030 then 'Professional' 
when 0x00000045 then 'Not supported' 
when 0x00000031 then 'Professional N' 
when 0x00000067 then 'Professional with Media Center' 
when 0x00000036 then 'Server For SB Solutions EM' 
when 0x00000033 then 'Server For SB Solutions' 
when 0x00000037 then 'Server For SB Solutions EM' 
when 0x00000018 then 'Windows Server 2008 for Windows Essential Server Solutions' 
when 0x00000023 then 'Windows Server 2008 without Hyper-V for Windows Essential Server Solutions' 
when 0x00000021 then 'Server Foundation' 
when 0x00000032 then 'Windows Small Business Server 2011 Essentials' 
when 0x00000009 then 'Windows Small Business Server' 
when 0x00000019 then 'Small Business Server Premium' 
when 0x0000003F then 'Small Business Server Premium (core installation)' 
when 0x00000038 then 'Windows MultiPoint Server' 
when 0x0000004F then 'Server Standard (evaluation installation)' 
when 0x00000007 then 'Server Standard' 
when 0x0000000D then 'Server Standard (core installation)' 
when 0x00000024 then 'Server Standard without Hyper-V' 
when 0x00000028 then 'Server Standard without Hyper-V (core installation)' 
when 0x00000034 then 'Server Solutions Premium' 
when 0x00000035 then 'Server Solutions Premium (core installation)' 
when 0x0000000b then 'Starter' 
when 0x00000042 then 'Not supported' 
when 0x0000002f then 'Starter N' 
when 0x00000017 then 'Storage Server Enterprise' 
when 0x0000002E then 'Storage Server Enterprise (core installation)' 
when 0x00000014 then 'Storage Server Express' 
when 0x0000002B then 'Storage Server Express (core installation)' 
when 0x00000060 then 'Storage Server Standard (evaluation installation)' 
when 0x00000015 then 'Storage Server Standard' 
when 0x0000002C then 'Storage Server Standard (core installation)' 
when 0x0000005F then 'Storage Server Workgroup (evaluation installation)' 
when 0x00000016 then 'Storage Server Workgroup' 
when 0x0000002D then 'Storage Server Workgroup (core installation)' 
when 0x00000000 then 'An unknown product' 
when 0x00000001 then 'Ultimate' 
when 0x00000047 then 'Not supported' 
when 0x0000001C then 'Ultimate N' 
when 0x00000011 then 'Web Server (full installation)' 
when 0x0000001D then 'Web Server (core installation)' 
else '(unknown)'
end as windows_product_type
, os_language_version
from sys.dm_os_windows_info
)
selectserverproperty('MachineName') as windows_server_name
, *
, case 
when charindex('(core', lower(windows_product_type)) > 0  then 'core'
when charindex('(license', lower(windows_product_type)) > 0 then 'licenses'
when charindex('(core', lower(windows_product_type)) > 0  then 'evaluation'
else '(unknown)' end as licensing_type
, case  when charindex('server', lower(windows_product_type)) > 0 
then windows_server_release_name + rtrim(' ' + windows_service_pack_level) + ' ' + case when left(windows_server_release_name, len(windows_product_type)) <> windows_product_type then windows_product_type else '' end
else windows_home_release_name + rtrim(' ' + windows_service_pack_level) + ' ' + case when left(windows_home_release_name, len(windows_product_type)) <> windows_product_type then windows_product_type else '' end
end as full_windows_name
fromCTE
)
go