Determining the version of SQL server from TSQL

The question came up as to which version of sql server is being run, and how do you determine the exact version number of the SQL server. Here is what I have come up with. From TSQL if you are connected to the SQL Server, you can run the following command:

SELECT 'SQL Server ' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

which should return something like the following:

SQL Server 9.00.4035.00 – SP3 (Standard Edition (64-bit))
or
SQL Server 10.0.2531.0 – SP1 (Enterprise Edition (64-bit))

Another way to do it is just

SELECT @@VERION;

Which will return something like this

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Additionally if you are at the command prompt on the SQL server itself, you can run the following command:

osql -E -q”SELECT @@VERSION”

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!