How to query the version number and other information related to SQL Server ?

Sometimes, you might want to query the  SQL Server version to know its exact version number and other information like Platform, Product Name etc.

Here’s a simple stored procedure in SQL Server that helps to retrieve not just the SQL Server version but also additional information about the SQL Server.

/* SENTHIL KUMAR

Query to retreive the SQL Server Information */

EXEC xp_msver

GO

The xp_msver returns not just the version number, but also other SQL Server related information like

1    ProductName    NULL    Microsoft SQL Server

2    ProductVersion    720896    11.0.2100.60

3    Language    1033    English (United States)

4    Platform    NULL    NT x64

5    Comments    NULL    SQL

6    CompanyName    NULL    Microsoft Corporation

7    FileDescription    NULL    SQL Server Windows NT – 64 Bit

8    FileVersion    NULL    2011.0110.2100.060 ((SQL11_RTM).120210-1917 )

9    InternalName    NULL    SQLSERVR

10    LegalCopyright    NULL    Microsoft Corp. All rights reserved.

11    LegalTrademarks    NULL    Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12    OriginalFilename    NULL    SQLSERVR.EXE

13    PrivateBuild    NULL    NULL

14    SpecialBuild    137625660    NULL

15    WindowsVersion    602931718    6.2 (9200)

16    ProcessorCount    8    8

17    ProcessorActiveMask    NULL     ff

18    ProcessorType    8664    NULL

19    PhysicalMemory    8124    8124 (8519024640)

20    Product ID    NULL    NULL

Note that I am using SQL Server 2012 for trying out the above query 🙂