Sys.dm_os_windows_info DMV to find Operating system information from SQL Server 2012
Microsoft always tries to give more & more power to SQL Developer. Today I come up with one more wonderful DMV named “Sys.dm_os_windows_info” which gives you information about windows operating where SQL Server Instance is installed. You can find this DMV in SQL Server 2008 R2 or later version.
As a Developer or DBA, it is mandatory to know the version of Operating System where SQL Server Instance is installed especially when you are developing script to read system information in Powershell or vbscript. It is not possible to know the operating system information if you don’t have your in-house server but depend on hosting company.
Here is the small and wonderful script to read information about Operating System right from your query windows or SQL Server 2012 or 2008 R2.
SELECT @@VERSION AS SQLVersion ,windows_release ,windows_service_pack_level ,windows_sku ,os_language_version FROM sys.dm_os_windows_info GO
Once you run above given query, you will get output something like this:
First column “SQLVersion” uses @@VERSION system variable to display SQL Server version.
Second column “Windows_Release” gives information about Operating system version.
Third column “Windows_Service_Pack_Level”, as name suggests, gives information about latest service pack installed on the top of operating system.
Fourth column “Windows_SKU” gives information about Windows Stock Keeping Units id.
Fifth & last column “os_language_version” shows the windows locale identifier. Each number represents language. 1033 represent English US. For more information about LCID (Windows Locale Identifier), click here.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.