Display SQL Server detailed version with a query

How to display the SQL Server detailed version number with a simple SQL Server query? Using SQL Server Management Studio, to get the exact version of the SQL Server installation, it is possible by using simple T-SQL queries. For example, it is very helpful to know the exact version of SQL Server installed. Especially when working on security patches and upgrades.

But also, when third-party software needs a specific version of SQL Server to work properly. To clarify, the SQL Server exact number is not only “SQL Server 2016 for example” but also the numbers that goes after, for example “Microsoft SQL Server 2016 – 13.0.5366.0”.

How to display the current SQL Server version with an SQL query ?

The answer to this question is simple and there are different ways to display the database server version.  The full list of SQL Server versions is available on the official sql server version web page.

First option is to use the @@VERSION system variable:  

SELECT @@VERSION; 

How to get the MS SQL current version with sp_server_info system stored procedure?

The second option to display the SQL Server detailed version number is to use a query with the sp_server_info system procedure : 

EXEC sp_server_info 2;

The result is the DBMS_VER system attribute which is the SQL Server version. In this example, the query displays the exact number in the attribute_value column and is:

Microsoft SQL Server 2008 R2 – 10.50.6000.34

In conclusion, these 2 different options are very quick to execute. The queries display the SQL Server version number with 2 different level of details.

This section of the blog explains some T-SQL tips like for example how to manage text larger then 8000 characters within a script.

Be the first to comment

Leave a Reply

Your email address will not be published.


*