In this blog, we will see how to get the installed product edition using "SERVERPROPERTY".

"SERVERPROPERTY"

"SERVERPROPERTY" is a System-defined Function used to return the SQL Server instance information.

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')
Get the installed Product Edition using "SERVERPROPERTY".

Use Edition property to get the installed product edition.

Example
  1. select SERVERPROPERTY('Edition') as 'Edition'
Output



Get the installed product edition based on EditionID using "SERVERPROPERTY".

-1534726760 Standard
1804890536 Enterprise
1872460670 Enterprise Edition: Core-based Licensing
610778273 Enterprise Evaluation
284895786 Business Intelligence
-2117995310 Developer
-1592396055 Express
-133711905 Express with Advanced Services
1293598313 Web
1674378470 SQL Database or SQL Data Warehouse
Example
  1. declare @EditionID as sql_variant
  2. set @EditionID= (select SERVERPROPERTY('EditionID'))
  3. select @EditionID as EditionID,
  4. case @EditionID
  5. when -1534726760 then 'Standard'
  6. when 1804890536 then 'Enterprise'
  7. when 1872460670 then 'Enterprise Edition: Core-based Licensing'
  8. when 610778273 then 'Enterprise Evaluation'
  9. when 284895786 then 'Business Intelligence'
  10. when -2117995310 then 'Developer'
  11. when -1592396055 then 'Express'
  12. when -133711905 then 'Express with Advanced Services'
  13. when 1293598313 then 'Web'
  14. when 1674378470 then 'SQL Database or SQL Data Warehouse'
  15. end as 'Edition Based on ID'
Output



Get the installed product edition based on database engine edition using "SERVERPROPERTY".

1 Personal or Desktop Engine
2 Standard
3 Enterprise
4 Express
5 SQL Database
6 SQL Data Warehouse

Example
  1. declare @EngineEdition as sql_variant
  2. set @EngineEdition = (select SERVERPROPERTY('EngineEdition'))
  3. select @EngineEdition as EngineEdition,
  4. case @EngineEdition
  5. when 1 then 'Personal or Desktop Engine'
  6. when 2 then 'Standard'
  7. when 3 then 'Enterprise'
  8. when 4 then 'Express'
  9. when 5 then ' SQL Database'
  10. when 6 then 'SQL Data Warehouse'
  11. end as 'Engine Edition Based on ID'
Output



Get the installed product edition using PowerShell

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following.
  • Open Windows PowerShell as Administrator
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.

      PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('Edition') as 'SQL Edition'" -ServerInstance "epm\epmdb" -Username sa -Password *****


Applies to
  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Reference

See also

Download the "Get The Detailed SQL Server Information" Script.