{"id":6457,"date":"2022-02-27T06:58:00","date_gmt":"2022-02-27T05:58:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6457"},"modified":"2022-12-12T18:29:18","modified_gmt":"2022-12-12T17:29:18","slug":"query-to-display-sql-server-version","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/dba\/query-to-display-sql-server-version\/","title":{"rendered":"Query to display SQL Server version details"},"content":{"rendered":"\n

Display the SQL Server version number with details using a query?<\/em><\/strong><\/h4>\n\n\n\n

Using SQL Server Management Studio, here is a query to display SQL Server exact version. It is possible by using simple T-SQL queries and stored procedures. For example, it is very helpful to know the exact version of SQL Server installed. Especially when working on security patches and upgrades<\/strong>, to ensure compatibility and stability. <\/p>\n\n\n\n

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<\/em>, or SQL Server 2019<\/em> for example but also the numbers that goes after, for example Microsoft SQL Server 2016 – 13.0.5366.0.<\/p>\n\n\n\n

How to display the SQL Server version with a query ?<\/h2>\n\n\n\n

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<\/a>. First option is to use the @@VERSION system variable<\/a>.<\/p>\n\n\n\n

SELECT @@VERSION; \n<\/pre>\n\n\n\n
Display MS SQL version with with a stored procedure<\/h2>\n\n\n\n
The second option to display the MS SQL detailed version number is to use a query with the sp_server_info<\/em> system procedur.<\/p>\n\n\n\n
EXEC sp_server_info 2;\n<\/pre>\n\n\n\n
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<\/em> column and is:<\/p>\n\n\n\n
attribute_id<\/em><\/td>attribute_name<\/em><\/td>attribute_value<\/em><\/td><\/tr>
2<\/strong><\/td>DBMS_VER<\/strong><\/td>Microsoft SQL Server 2019 – 15.0.2095.3<\/strong><\/td><\/tr><\/tbody><\/table>
Result of the query to display the SQL Server version in SSMS<\/figcaption><\/figure>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
Tutorials on MS database administration<\/h2>\n\n\n\n