SQL SERVER – System procedures to know SQL Server Version

March 28, 2015

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)

SQL Extended Events, SQL Stored Procedure

SQL SERVER – Find the Growth Size for All files in All Databases

Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

8 Comments. Leave new

ssn
March 28, 2015 7:17 pm
superb…….sir……….
Reply
- Pinal Dave
  March 31, 2015 6:16 am
  ssn – I am glad that you liked it.
  Reply
ssn
March 28, 2015 7:29 pm
sir…the 2nd one is showing the error “Could not find stored procedure ‘xp_msver’.”
I’m using sql server version 2000 sir………
Reply
- Pinal Dave
  March 31, 2015 6:15 am
  It should work in SQL 2000. https://www.microsoft.com/en-us/download/details.aspx?id=51958
  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
- Pinal Dave
  April 9, 2015 6:52 am
  Shane Bryant
  Yeah, i too learnt that recently :)
  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
- Pinal Dave
  June 29, 2015 12:39 pm
  “(Build X: Service Pack 1) -> is for operating system and has NO relation with SQL version.
  Reply