One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Friday, March 24, 2017

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!



4 comments:

  1. This works even better in SQL Server vNext:

    SELECT host_platform, host_distribution, host_release,
    host_service_pack_level, host_sku, os_language_version
    FROM sys.dm_os_host_info;

    ReplyDelete
  2. About the comment "you want to know every single KB applied to your server, ever." - Actually, I would, but this doesn't provide this. It dies after showing about 245 hotfixes. On our 2008R2 servers, it will show half of the name of 246th hotfix, then proceed to the name of the network cards. According to Windows Update, the OS has received 286 updates on this machine.

    Rob

    ReplyDelete
    Replies
    1. 246 is an interesting cutoff - not a power of 2 or anything like that...thanks for the information!

      Delete