Wednesday, November 28, 2012

Goodbye @@version, hello xp_msver...

Ginktage - 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.


xp_msver (Transact-SQL)

Returns version information about Microsoft SQL Server. xp_msver also returns information about the actual build number of the server and information about the server environment. The information that xp_msver returns can be used within Transact-SQL statements, batches, stored procedures, and so on, to enhance logic for platform-independent code.

xp_msver [ optname ]     


Is the name of an option, and can be one of the following values.

Option/Column name



Product name; for example, Microsoft SQL Server.


Product version.


The language version of SQL Server.


Operating-system name, manufacturer name, and chip family name for the computer that is running SQL Server.


Miscellaneous information about SQL Server.


Result Sets

xp_msver, without any parameters, returns a four-column result set that lists all the option values. xp_msver, for any parameter, returns the four-column result set with values for that option.


Requires membership in the public role.


So it only looks like xp_msver has been around since SQL Server 2005... sigh... Still better to know now than to never know (and that's my story and I'm going to stick with it! )


Related Past Post XRef:
"Select @@Version" is old school... Check out the ServerProperty function

SQL Server Version Number Database (SQL Server 6.5 through 2008 R2 SP1 CTP...)
SQL Server Version Numbers - That @@version number means my SQL Server is running what SP?

No comments: