Querying SQL and Windows Version Info with T-SQL

Just a quick one today - I see questions sometimes about polling Windows information from inside SQL Server itself.  There are a couple of frequently touted options:

--

(1)  SELECT @@VERSION

The most basic option, and it does return most of what we want but not in any kind of a pretty format:


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27                  
Copyright (c) Microsoft Corporation                 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

To just run a query and see the answer this is fine, but usually I like to be able to programmatically manipulate the data (such as an ORDER BY), and a result set that is one big text field (with embedded line feeds) is not a great way to go.

--

(2) exec master..xp_cmdshell 'systeminfo'

This is sort of cheating as it requires a call to xp_cmdshell to call a Windows command rather than anything truly inside SQL Server, but it does work (assuming you have xp_cmdshell enabled):

Host Name:               Instance01

OS Name:                   Microsoft Windows Server 2008 R2 Standard

OS Version:                6.1.7601 Service Pack 1 Build 7601

OS Manufacturer:           Microsoft Corporation

OS Configuration:          Member Server

OS Build Type:             Multiprocessor Free

Registered Owner:         MyCompany

Registered Organization:   MyCompany

Product ID:                00477-001-0000421-84319

Original Install Date:     3/13/2013, 8:28:33 AM

System Boot Time:          1/28/2017, 8:03:41 AM

System Manufacturer:       VMware, Inc.

System Model:              VMware Virtual Platform

System Type:               x64-based PC

Processor(s):              4 Processor(s) Installed.

[01]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[02]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[03]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[04]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

BIOS Version:              Phoenix Technologies LTD 6.00, 9/21/2015

Windows Directory:         C:\Windows

System Directory:          C:\Windows\system32

Boot Device:               \Device\HarddiskVolume1

System Locale:             en-us;English (United States)

Input Locale:              en-us;English (United States)

Time Zone:                 (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

Total Physical Memory:     24,576 MB

Available Physical Memory: 5,405 MB

Virtual Memory: Max Size:  25,598 MB

Virtual Memory: Available: 6,182 MB

Virtual Memory: In Use:    19,416 MB

Page File Location(s):     D:\pagefile.sys

Domain:                   mydomain.com

Logon Server:              N/A

Hotfix(s):                 143 Hotfix(s) Installed.

[01]: KB2470949

[02]: KB2509553

[03]: KB2511455

[04]: KB2547244

[05]: KB2560656

[06]: KB2570947

[07]: KB2585542

[08]: KB2604115

[09]: KB2621440

[10]: KB2644615

[11]: KB2654428

[12]: KB2667402

[13]: KB2676562

[14]: KB2690533

[15]: KB2692929

[16]: KB2698365

[17]: KB2705219

[18]: KB2709715

[19]: KB2724197

[20]: KB2736422

[21]: KB2742599

[22]: KB2758857

[23]: KB2765809

[24]: KB2770660

[25]: KB2799494

[26]: KB2807986

[27]: KB2813170

[28]: KB2813347

[29]: KB2813430

[30]: KB2840149

[31]: KB2840631

[32]: KB2861698

[33]: KB2862152

[34]: KB2862330

[35]: KB2862335

[36]: KB2862973

[37]: KB2864202

[38]: KB2868038

[39]: KB2871997

[40]: KB2884256

[41]: KB2892074

[42]: KB2893294

[43]: KB2894844

[44]: KB2898851

[45]: KB2911501

[46]: KB2931356

[47]: KB2937610

[48]: KB2943357

[49]: KB2957189

[50]: KB2968294

[51]: KB2972100

[52]: KB2972211

[53]: KB2973112

[54]: KB2973201

[55]: KB2973351

[56]: KB2977292

[57]: KB2978120

[58]: KB2984972

[59]: KB2991963

[60]: KB2992611

[61]: KB3000483

[62]: KB3003743

[63]: KB3004361

[64]: KB3004375

[65]: KB3010788

[66]: KB3011780

[67]: KB3018238

[68]: KB3019978

[69]: KB3021674

[70]: KB3022777

[71]: KB3023215

[72]: KB3030377

[73]: KB3033889

[74]: KB3035126

[75]: KB3037574

[76]: KB3038314

[77]: KB3042553

[78]: KB3045685

[79]: KB3046017

[80]: KB3046269

[81]: KB3055642

[82]: KB3059317

[83]: KB3060716

[84]: KB3068457

[85]: KB3071756

[86]: KB3072305

[87]: KB3072630

[88]: KB3074543

[89]: KB3075220

[90]: KB3076895

[91]: KB3078601

[92]: KB3080446

[93]: KB3084135

[94]: KB3086255

[95]: KB3092601

[96]: KB3097989

[97]: KB3101722

[98]: KB3108371

[99]: KB3108381

[100]: KB3108664

[101]: KB3108670

[102]: KB3109103

[103]: KB3109560

[104]: KB3110329

[105]: KB3122648

[106]: KB3123479

[107]: KB3124275

[108]: KB3126587

[109]: KB3127220

[110]: KB3133043

[111]: KB3135983

[112]: KB3139398

[113]: KB3139914

[114]: KB3139940

[115]: KB3142024

[116]: KB3142042

[117]: KB3145739

[118]: KB3146706

[119]: KB3146963

[120]: KB3149090

[121]: KB3156016

[122]: KB3156017

[123]: KB3156019

[124]: KB3159398

[125]: KB3161949

[126]: KB3161958

[127]: KB3163245

[128]: KB3164033

[129]: KB3164035

[130]: KB3170455

[131]: KB3177186

[132]: KB3184122

[133]: KB3185911

[134]: KB3188740

[135]: KB3192321

[136]: KB3192391

[137]: KB3205394

[138]: KB3210131

[139]: KB3212642

[140]: KB958488

[141]: KB976902

[142]: KB976932

[143]: KB3212646

Network Card(s):           1 NIC(s) Installed.

[01]: Intel(R) PRO/1000 MT Network Connection

Connection Name: LAN Prod

DHCP Enabled:    No

IP address(es)

[01]: 192.168.22.33

https://i.ytimg.com/vi/eOJ32gNM0qc/hqdefault.jpg 

Well....assuming you have xp_cmdshell enabled *and* you want to know every single KB applied to your server, ever.

Again this isn't cleanly parseable, so it isn't the optimal answer.

--

(3) Glenn Berry

If you have read my blog at all you know I am a huge fan of Glenn Alan Berry's (blog/@GlennAlanBerry) "Diagnostic Information Queries" - commonly referred to as the "DMV Queries." (as seen here, here, here, and here.)  

http://s2.quickmeme.com/img/99/995c9a89f2eb1f869fb6fd7fc72ac143a76d6313f05cb0b8309813514eb0f876.jpg

Glenn has been maintaining this forever, and does an amazing job of both deciphering the DMVs and of cataloging submissions from other prominent SQL Server professionals (such as Jimmy May's great Disk Latency query).

The first relevant query is currently Query #1:

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];

As you can see this is just the combination of @@VERSION described above and @@SERVERNAME to return the instance's name.

The next (and more interesting) query is Query #3:

SELECT SERVERPROPERTY('MachineName') AS [MachineName], 

SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],                                                       -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],         -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
SERVERPROPERTY('ProductBuild') AS [ProductBuild],
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],                    -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version]; 

MachineName	ServerName	Instance	IsClustered
INSTANCE01	INSTANCE01	NULL	0
ComputerNamePhysicalNetBIOS	Edition	ProductLevel	ProductUpdateLevel
INSTANCE01	Enterprise Edition (64-bit)	SP3	NULL
ProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
11.0.6020.0	11	0	6020
ProductBuildType	ProductUpdateReference	ProcessID	Collation
NULL	KB3072779	1988	Latin1_General_CI_AI
IsFullTextInstalled	IsIntegratedSecurityOnly	FilestreamConfiguredLevel	IsHadrEnabled
1	0	0	0
HadrManagerStatus	IsXTPSupported	Build CLR Version
2	NULL	v4.0.30319

This query introduces the concept of the SERVERPROPERTY() function and shows some of the many fields that it can retrieve.  On of the nicest things about SERVERPROPERTY() to me is that it cleanly parses out the Major and Minor versions of SQL Server - this is the most direct way to find out that your instance is Version 11 (SQL 2012).

The limitation for our purposes here is that it doesn't return any information about the Operating System version (here we are talking about Windows because these hooks just don't play into Linux)...you get a little external information like the NETBIOS name, but nothing about the actual Windows Version.

We need something more...

--

(4) My Answer

As is often the case, my best answer is a cross between a couple of the previous options to take the best of both... kind of like this:

http://weknowmemes.com/wp-content/uploads/2014/02/hilarious-animal-hybrids.jpg

(Sorry - I found that picture and couldn't resist...)


This query uses string functions to parse @@VERSION and extract some of the information (and then returns pretty text via a pair of CASE statements) and also uses some of the more meaningful SERVERPROPERTY() values.

The string to parse the Windows Version Number out of @@VERSION came from here - much easier than trying to backtrack it myself!

SELECT SERVERPROPERTY('ServerName') AS [SQLServerName]

, SERVERPROPERTY('ProductVersion') AS [SQLProductVersion]

, SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion]
, SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion]
, SERVERPROPERTY('ProductBuild') AS [ProductBuild]
, CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4) 

WHEN '8.00' THEN 'SQL Server 2000'

WHEN '9.00' THEN 'SQL Server 2005'

WHEN '10.0' THEN 'SQL Server 2008'

WHEN '10.5' THEN 'SQL Server 2008 R2'

WHEN '11.0' THEN 'SQL Server 2012'

WHEN '12.0' THEN 'SQL Server 2014'

ELSE 'SQL Server 2016+'

END AS [SQLVersionBuild]

, SERVERPROPERTY('ProductLevel') AS [SQLServicePack]

, SERVERPROPERTY('Edition') AS [SQLEdition]

, RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3) as [WindowsVersionNumber]

, CASE RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)

WHEN '5.0' THEN 'Windows 2000'

WHEN '5.1' THEN 'Windows XP'

WHEN '5.2' THEN 'Windows Server 2003/2003 R2'

WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'

WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'

WHEN '6.2' THEN 'Windows Server 2012/Windows 8'

ELSE 'Windows 2012 R2+'

END AS [WindowsVersionBuild]

This allows me to return a pretty result set and also to extract number values and simple strings that I can then programmatically act upon (or sort/filter in Excel):

SQLServerName	SQLProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
INSTANCE01	11.0.6020.0	11	0	6020
SQLVersionBuild	SQLServicePack	SQLEdition	WindowsVersionNumber	WindowsVersionBuild
SQL Server 2012	SP3	Enterprise Edition (64-bit)	6.1	Windows Server 2008 R2/Windows 7

--

Hope this helps!