Determining which version and edition of SQL Server Database Engine is running by SQL Script.

Summary

In this sample we will talk about how to determine the version and edition of SQL Server Database Engine that is running by SQL Script. T-SQL can be used to get the current version of the latest Service Pack and Cumulative Update. The Current SQL Server can be upgraded to some specific editions. 

Script 

You can use the script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code from DetermineVersionOfSQLServer.sql, paste it and run.

 

An example of the output of this query is as below:  

---------------------------------------------------------------------------------------------------------

--//Your current Microsoft SQL Server information:

---------------------------------------------------------------------------------------------------------

Product Version:          11.0.5634.1

Product Name:             SQL Server 2012

Product Level:            SP2

Product Edition:          Enterprise Edition: Core-based Licensing (64-bit)

---------------------------------------------------------------------------------------------------------

Note, if you want to know the information about CU, please read this KB below.

KB321185, <https://support.microsoft.com/en-us/kb/321185>

---------------------------------------------------------------------------------------------------------

Support Lifecycle stage: Mainstream Support Phase. For additional information refer to

https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO, and Q6, Q18

in the FAQ section of Support Lifecycle page at: https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy

---------------------------------------------------------------------------------------------------------

Full information:

Microsoft SQL Server 2012 (SP2-CU8) (KB3082561) - 11.0.5634.1 (X64)

    Sep  3 2015 18:55:08

    Copyright (c) Microsoft Corporation

    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 10240: )

 

---------------------------------------------------------------------------------------------------------

--//Recommended updates:

--### RTM -> QFE or GDR

--### SP  -> QFE or GDR

--### QFE -> QFE

--### GDR -> GDR or QFE

---------------------------------------------------------------------------------------------------------

Install the latest service pack:             SP4, <https://support.microsoft.com/en-us/kb/4018073>

Install the latest Cumulative Update (CU) of SP4:  RTW/PCU4, <https://support.microsoft.com/en-us/kb/4018073>

---------------------------------------------------------------------------------------------------------

###### QFE branch updates

---------------------------------------------------------------------------------------------------------

11.0.2376 (SQL Server 2012 RTM QFE) http://support.microsoft.com/en-us/kb/2716441

11.0.3513 (SQL Server 2012 SP1 QFE) https://support.microsoft.com/en-us/kb/3045317

11.0.5613 (SQL Server 2012 SP2 QFE) https://support.microsoft.com/en-us/kb/3045319

---------------------------------------------------------------------------------------------------------

Note, if you don’t want to upgrade to the latest service pack right now, we recommend you install the latest

Cumulative Update CU9 of SQL Server 2012 SP2.

Install the latest Cumulative Update (CU) of SP2: CU9, <https://support.microsoft.com/en-us/kb/3098512>

 

---------------------------------------------------------------------------------------------------------

--//You can upgrade to any of the following product(s):

---------------------------------------------------------------------------------------------------------

SQL Server 2014 Enterprise

SQL Server 2014 Business Intelligence

SQL Server 2016 Enterprise

SQL Server 2016 Business Intelligence

SQL Server 2017 Enterprise

SQL Server 2017 Business Intelligence

 

For additional information about supported version and edition upgrades, please refer to:

https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

 

Here are some code snippets for your reference.

SQL
Edit|Remove
SET @EditionID            = SERVERPROPERTY('EditionID'SET @ProductVersion        = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductVersion'))  
SET @ProductLevel        = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductLevel'))  
SET @UpdateLevel        = ISNULL(CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductUpdateLevel')),''SET @UpdateRef            = ISNULL(CONVERT(NVARCHAR(20),SERVERPROPERTY('@UpdateRef')),''SET @Edition            = CONVERT(NVARCHAR(100),SERVERPROPERTY('Edition')) 
 
SELECT    @ProductName =  
        CASE SUBSTRING(@ProductVersion,1,4) 
            WHEN '14.0' THEN 'SQL Server 2017' 
            WHEN '13.0' THEN 'SQL Server 2016'  
            WHEN '12.0' THEN 'SQL Server 2014'  
            WHEN '11.0' THEN 'SQL Server 2012'  
            WHEN '10.5' THEN 'SQL Server 2008 R2'  
            WHEN '10.0' THEN 'SQL Server 2008'   
        END, 
        @TheLastVersion =  
        CASE SUBSTRING(@ProductVersion,1,4) 
            WHEN '14.0' THEN 'SQL Server 2017 RTM'  
            WHEN '13.0' THEN 'SQL Server 2016 SP1'  
            WHEN '12.0' THEN 'SQL Server 2014 SP2'  
            WHEN '11.0' THEN 'SQL Server 2012 SP4'  
            WHEN '10.5' THEN 'SQL Server 2008 R2 SP3'  
            WHEN '10.0' THEN 'SQL Server 2008 SP4'  
        END

The first section is about the current SQL Server information.

The second section will display the latest Service Pack and Cumulative Update of current product.

The third section lists the versions that you can upgrade to.

Note: This section only supports Enterprise Edition, Standard Edition, Developer Edition, Express Edition. If you are using other versions, use the following link address to see whether can upgrade to the desired version.

 

In the next chapter, you will be demonstrated with how to determine the version of the installed components in SQL Server by PowerShell.

 

For information:

               About determining the version and edition of SQL Server:

                https://support.microsoft.com/en-us/kb/321185

                About support version and edition upgrade:

                https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

                About Microsoft support lifecycle:

                http://blogs.msdn.com/b/psssql/archive/2010/02/17/mainstream-vs-extended-support-and-sql-server-2005-sp4-can-someone-explain-all-of-this.aspx

                https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy

                https://support.microsoft.com/en-gb/lifecycle/search

Prerequisites

SQL Server 2008 or higher

---------------------------------------------------------------------------------------------------------
--//Your current Microsoft SQL Server information:
---------------------------------------------------------------------------------------------------------
Product Version:          11.0.3513.0
Product Name:             SQL Server 2012
Product Level:            SP1
Product Edition:          Enterprise Edition: Core-based Licensing (64-bit)
---------------------------------------------------------------------------------------------------------
Note, if you want to know information about CU, please read this KB below.
KB321185, <https://support.microsoft.com/en-us/kb/321185>
---------------------------------------------------------------------------------------------------------
Support Lifecycle stage: Mainstream Support Phase, however security/non-security updates are not available for
current 2012 baseline. Upgrade to SP2 for security/Non-security updates. For additional information refer to:
https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL%20Server&Filter=FilterNO, and Q6, Q18
in the FAQ section of Support Lifecycle page at: https://support.microsoft.com/en-us/lifecycle#gp/lifePolicy
---------------------------------------------------------------------------------------------------------
Full information:
Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) 
 May  4 2015 18:58:47 
 Copyright (c) Microsoft Corporation
 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 10240: )
---------------------------------------------------------------------------------------------------------
--//Recommended update:
---------------------------------------------------------------------------------------------------------
Install the latest service pack:              SP2, <https://support.microsoft.com/en-us/kb/2958429>
Install the latest Cumulative Update (CU) of SP2:  CU8, <https://support.microsoft.com/en-us/kb/3082561>
---------------------------------------------------------------------------------------------------------
--//You can upgrade to any of the following product(s):
---------------------------------------------------------------------------------------------------------
SQL Server 2014 Enterprise
SQL Server 2014 Business Intelligence
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
For additional information about supported version and edition upgrades refer to:
https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx