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
- SERVERPROPERTY ('propertyname')
Get the installed Product Edition using "SERVERPROPERTY".Use Edition property to get the installed product edition.
Example
- 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
- declare @EditionID as sql_variant
- set @EditionID= (select SERVERPROPERTY('EditionID'))
- select @EditionID as EditionID,
- case @EditionID
- when -1534726760 then 'Standard'
- when 1804890536 then 'Enterprise'
- when 1872460670 then 'Enterprise Edition: Core-based Licensing'
- when 610778273 then 'Enterprise Evaluation'
- when 284895786 then 'Business Intelligence'
- when -2117995310 then 'Developer'
- when -1592396055 then 'Express'
- when -133711905 then 'Express with Advanced Services'
- when 1293598313 then 'Web'
- when 1674378470 then 'SQL Database or SQL Data Warehouse'
- 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
- declare @EngineEdition as sql_variant
- set @EngineEdition = (select SERVERPROPERTY('EngineEdition'))
- select @EngineEdition as EngineEdition,
- case @EngineEdition
- when 1 then 'Personal or Desktop Engine'
- when 2 then 'Standard'
- when 3 then 'Enterprise'
- when 4 then 'Express'
- when 5 then ' SQL Database'
- when 6 then 'SQL Data Warehouse'
- 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
Hadshana KamalanathanPosted Jul 16, 2018, 10:59 AM
Thank you for sharing.
Manish KumarPosted Dec 7, 2017, 2:41 AM
Sir, what about select @@version