How can I find out which version of SQL Server is running on a machine?

Question

I am running SQL Server 2005, but I am not sure which edition it is. How can I decide which version (Express, Standard, Enterprise, etc.) is running on the machine?

Answer 1

select @@version 

Output example

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

If you just want to get a publication, you can use:

 select serverproperty('Edition') 

For use in an automatic script, you can get the edition identifier, which is an integer:

 select serverproperty('EditionID') 

• -1253826760 = Desktop
• -1592396055 = Express
• -1534726760 = Standard
• 1333529388 = Working Group
• 1804890536 = Enterprise
• -323382091 = Personal
• -2117995310 = Developer
• 610778273 = Enterprise valuation
• 1044790755 = Windows Embedded SQL
• 4161255391 = Express with advanced services

Answer 2

I use this query here to get all the relevant information (related to me, at least :-)) from SQL Server:

 SELECT SERVERPROPERTY('productversion') as 'Product Version', SERVERPROPERTY('productlevel') as 'Product Level', SERVERPROPERTY('edition') as 'Product Edition', SERVERPROPERTY('buildclrversion') as 'CLR Version', SERVERPROPERTY('collation') as 'Default Collation', SERVERPROPERTY('instancename') as 'Instance', SERVERPROPERTY('lcid') as 'LCID', SERVERPROPERTY('servername') as 'Server Name' 

This gives you a result something like this:

 Product Version Product Level Product Edition CLR Version 10.0.2531.0 SP1 Developer Edition (64-bit) v2.0.50727 Default Collation Instance LCID Server Name Latin1_General_CI_AS NULL 1033 ********* 

Answer 3

You can only get the title of the publication using the following steps.

• Open SQL Server Configuration Manager
• In the list of SQL Server Services, right-click on “SQL Server (Instance Name)” and select “Properties”.
• Select the Advanced tab in the Properties window.
• Confirm the name of the version in the section "Name of inventory unit"
• Check Version ID from "Storage Unit ID"
• Check Service Pack with "Service Pack Level"
• Check version from "Version"

screenshot

Answer 4

You can only get the edition (plus for individual properties) using SERVERPROPERTY

eg

 SELECT SERVERPROPERTY('Edition') 

Quote (for "Edition"):

The installed edition of the SQL Server instance product. Use the value of this property to determine features and limitations, such as the maximum number of processors that are supported by the installed product.
Returns:
"Desktop Engine" (not available for SQL Server 2005.)
'Developer Edition'
"Enterprise Edition"
"Enterprise Evaluation Edition"
Personal Version (not available for SQL Server 2005.)
'Standard version'
Express Edition
Express Edition with Advanced Services
"Editorial team"
"Windows Embedded SQL"
Base data type: nvarchar (128)

Answer 5

 SELECT CASE WHEN SERVERPROPERTY('EditionID') = -1253826760 THEN 'Desktop' WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express' WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard' WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup' WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise' WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal' WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer' WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Windows Embedded SQL' WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express with Advanced Services' END AS 'Edition';