Blog Post

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!


Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads