How to Tell What Edition of SQL Server Runs on the MAChine

How can I tell what edition of SQL Server runs on the machine?

select @@version

Sample Output


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

If you just want to get the edition, you can use:

select serverproperty('Edition')

To use in an automated script, you can get the edition ID, which is an integer:

select serverproperty('EditionID')
  • -1253826760 = Desktop
  • -1592396055 = Express
  • -1534726760 = Standard
  • 1333529388 = Workgroup
  • 1804890536 = Enterprise
  • -323382091 = Personal
  • -2117995310 = Developer
  • 610778273 = Enterprise Evaluation
  • 1044790755 = Windows Embedded SQL
  • 4161255391 = Express with Advanced Services

How to check whether the installed instance is full SQL Server or just SQL Server Express

You can look at the installed instances in the registry key:

Software\Microsoft\Microsoft SQL Server\InstalledInstances

This will contain all the installed instances, e.g. on my system:

MSSQLSERVER
SQLEXPRESS

Go into this registry key with this value:

Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

to get the actual instance name that you need in the next step.

Now if you go look at the registry key:

Software\Microsoft\Microsoft SQL Server\(InstanceName)\Setup\Edition

there you have a value of e.g. Express for a SQL Server Express, or Developer Edition or something else. That should tell you if you have Express or another, "full" edition of SQL Server

How to check SQL Server version

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

How can I determine installed SQL Server instances and their versions?

At a command line:

SQLCMD -L

or

OSQL -L

(Note: must be a capital L)

This will list all the sql servers installed on your network. There are configuration options you can set to prevent a SQL Server from showing in the list. To do this...

At command line:

svrnetcn

In the enabled protocols list, select 'TCP/IP', then click properties. There is a check box for 'Hide server'.

SQL Server file names vs versions

The mapping is (along with links to build lists):






























































Major Version /
Compatibility Level
Version LabelFile Version
80SQL Server 20008.00.xxxx
90SQL Server 20059.00.xxxx
100SQL Server 200810.00.xxxx
105SQL Server 2008 R210.50.xxxx
110SQL Server 201211.00.xxxx
120SQL Server 201412.00.xxxx
130SQL Server 201613.00.xxxx
140SQL Server 201714.00.xxxx
150SQL Server 201915.00.xxxx
160SQL Server 202216.00.xxxx

How do I check for the SQL Server Version using Powershell?

Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3

http://msdn.microsoft.com/en-us/library/cc281847.aspx

SQL server shows different versions when seen from about screen and from query

You can see version of SQL Server on local computer by using Help\About. but when run select @@Version, you see version of SQL Server database engine that login on it. In other word you run SSMS from local computer and Help\About show version of SSMS. and then login to sql server database engine of your server, Select @@Version show version of sql server on the server.



Related Topics



Leave a reply



Submit