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.

Syntax
xp_msver [ optname ]     

optname

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

Option/Column name

Description

ProductName

Product name; for example, Microsoft SQL Server.

ProductVersion

Product version.

Language

The language version of SQL Server.

Platform

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

Comments

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.

Permissions

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?