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.
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 |
Well....assuming you have xp_cmdshell enabled *and* you want to know every single KB applied to your server, ever.
![]() |
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).
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 |
![]() |
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.
, SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion]
, SERVERPROPERTY('ProductBuild') AS [ProductBuild]
, CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4)
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 |