SQL SERVER – System procedures to know SQL Server Version

There are several ways to know the version of SQL Server. But did you know that there are two system procedures through which you can know the version? They are SP_SERVER_INFO and EXEC XP_MSVER

EXEC sp_SERVER_INFO

If you execute the above, you get a result set with informations about the server. The second will tell you the version number

Result is

attribute_id attribute_name attribute_value
———— ——————– —————-
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 – 11.0.3000.0
.
.
.
.
EXEC xp_MSVER

This is also very similar to SP_SERVER_INFO which will show you the version number (second row from the result set)

Result is

Index Name Internal_Value Character_Value
—— ——————————– ————– ——————-
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 720896 11.0.3153.0
.
.
.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Find the Growth Size for All files in All Databases
Next Post
Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

Related Posts

8 Comments. Leave new

  • superb…….sir……….

    Reply
  • sir…the 2nd one is showing the error “Could not find stored procedure ‘xp_msver’.”
    I’m using sql server version 2000 sir………

    Reply
  • Shane Bryant
    April 7, 2015 9:12 pm

    Awesome, Thanks :) The only one I knew was “select @@VERSION”. That one is kind of a jumbled-up mess, but it gives info about both the SQL version and OS version, like “Microsoft SQL Server 2008 R2 (SP2) – 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)”

    Reply
  • David Zokaites
    June 25, 2015 1:15 am

    Dave,

    It turns out that determining your SQL Server version is trickier than it should be. I found 6 different queries to get version information, and used two different queries to get the information I need. This situation seems ridiculous to me!

    — determine SQL Server version

    — provides year (i.e. 2014), version (i.e. 12.0.2000.8), edition (i.e. Enterprise), but not product level (i.e. RTM)
    — be sure to ignore “(Build X: Service Pack 1)” because it is apparently incorrect!
    select @@version
    go

    select serverProperty (‘ProductLevel’) — best way to get ProductLevel (i.e. RTM)
    go

    /*
    — not very useful
    select serverProperty (‘ProductVersion’) — repeats some info from select @@version
    select serverProperty (‘Edition’) — repeats some info from select @@version
    exec sp_server_info
    exec xp_msver
    go
    */

    Reply

Leave a Reply

Menu