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

Sql Problem Overview

I am running SQL Server 2005 but I am unsure which edition this is. How can I decide what edition (Express, Standard, Enterprise etc) is running on the machine?

Sql Solutions

Solution 1 - Sql

select @@version

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

Solution 2 - Sql

I use this query here to get all relevant info (relevant for 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'

That gives you an output 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	*********

Solution 3 - Sql

You can get just the edition name by using the following steps.

Open "SQL Server Configuration Manager"
From the List of SQL Server Services, Right Click on "SQL Server (Instance_name)" and Select Properties.
Select "Advanced" Tab from the Properties window.
Verify Edition Name from the "Stock Keeping Unit Name"
Verify Edition Id from the "Stock Keeping Unit Id"
Verify Service Pack from the "Service Pack Level"
Verify Version from the "Version"

screen shot

Solution 4 - Sql

You can get just the edition (plus under individual properties) using SERVERPROPERTY

e.g.

SELECT SERVERPROPERTY('Edition')

Quote (for "Edition"):

> Installed product edition of the > instance of SQL Server. Use the value > of this property to determine the > features and the limits, such as > maximum number of CPUs, that are > supported by the installed product.
> Returns:
'Desktop Engine' (Not > available for SQL Server 2005.)
> 'Developer Edition'
'Enterprise > Edition'
'Enterprise Evaluation > Edition'
'Personal Edition'(Not > available for SQL Server 2005.)
> 'Standard Edition'
'Express Edition'
> 'Express Edition with Advanced > Services'
'Workgroup Edition'
'Windows > Embedded SQL'
Base data type: > nvarchar(128)

Solution 5 - Sql

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';

Content Type	Original Author	Original Content on Stackoverflow
Question	gyurisc	View Question on Stackoverflow
Solution 1 - Sql	mmx	View Answer on Stackoverflow
Solution 2 - Sql	marc_s	View Answer on Stackoverflow
Solution 3 - Sql	Prashant Vadher	View Answer on Stackoverflow
Solution 4 - Sql	AdaTheDev	View Answer on Stackoverflow
Solution 5 - Sql	Hossein Kohzadi	View Answer on Stackoverflow