How to check SQL Server version

133

What are the possible ways to determine the deployed SQL Server version?

I’ve tried to do it using the SQL Server software. I want to do it using a command line SQL statement.

This question is tagged with sql-server-2008 version

~ Asked on 2013-12-03 12:48:36

The Best Answer is


240

Following are possible ways to see the version:

Method 1: Connect to the instance of SQL Server, and then run the following query:

Select @@version

An example of the output of this query is as follows:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 
10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express 
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Method 2: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

Method 3: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:

2011-03-27 22:31:33.50 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)                 Mar 29 2009 10:11:52                 Copyright (c) 1988-2008 Microsoft Corporation                Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

As you can see, this entry gives all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.

Method 4: Connect to the instance of SQL Server, and then run the following query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Note This query works with any instance of SQL Server 2000 or of a later version

~ Answered on 2013-12-03 12:49:28


9

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

Gives 8.00, 9.00, 10.00 and 10.50 for SQL 2000, 2005, 2008 and 2008R2 respectively.

Also, Try the system extended procedure xp_msver. You can call this stored procedure like

exec master..xp_msver

~ Answered on 2013-12-03 12:55:27


Most Viewed Questions: