How-to: Find the SQL Server Version and Service pack

To quickly return the version information from an SQL prompt:

SQL> select @@VERSION

You can also select the individual properties:

SELECT 'Version '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ') :: ' + @@VERSION as 'SQL Server version'

Show the database collation:

SQL> SELECT DATABASEPROPERTYEX('your_database_name', 'Collation') SQLversion;

A full script to display the version, service pack, update level, edition and the last SP avaiable:

--------------------------------------------------------------------------------- 
--  SQL Server Version, level and edition.
--------------------------------------------------------------------------------- 

DECLARE @EditionID       NVARCHAR(20)
DECLARE @Edition         NVARCHAR(100)
DECLARE @ProductLevel    NVARCHAR(20)
DECLARE @ProductVersion  NVARCHAR(20)
DECLARE @UpdateLevel     NVARCHAR(20)
DECLARE @ProductName     NVARCHAR(30)
DECLARE @TheLastVersion  NVARCHAR(100)

SET @EditionID       = CONVERT(NVARCHAR(20),SERVERPROPERTY('EditionID'))
SET @Edition         = CONVERT(NVARCHAR(100),SERVERPROPERTY('Edition'))
SET @ProductLevel    = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductLevel'))
SET @ProductVersion  = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductVersion')) 
SET @UpdateLevel     = ISNULL(CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductUpdateLevel')),'')


SELECT   @ProductName = 
   CASE SUBSTRING(@ProductVersion,1,4)
      WHEN '15.0' THEN 'SQL Server 2019'
      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 '15.0' THEN 'SQL Server 2019 RTM'
         WHEN '14.0' THEN 'SQL Server 2017 RTM' 
         WHEN '13.0' THEN 'SQL Server 2016 SP2' 
         WHEN '12.0' THEN 'SQL Server 2014 SP3' 
         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

-- Display all the versions:

PRINT REPLICATE('-',105)
PRINT '--//Your current Microsoft SQL Server information:'
PRINT REPLICATE('-',105)
PRINT 'Product Version:          ' + @ProductVersion
PRINT 'Product Name:             ' + @ProductName
PRINT 'Product Level:            ' + @ProductLevel
PRINT 'Product Edition:          ' + @Edition
PRINT 'Product EditionID:        ' + @EditionID
PRINT 'Update Level:             ' + @UpdateLevel
PRINT 'Final Version available:  ' + @TheLastVersion 
PRINT REPLICATE('-',105)
PRINT 'Full information:' + CHAR(13) + @@VERSION

“I'd call it a new version of voodoo economics, but I'm afraid that would give witch doctors a bad name” - Geraldine A. Ferraro

Related commands

Q321185 - Find the SQL Server version
SQL Server Version numbers