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
Q321185 - Find the SQL Server version
SQL Server Version numbers