CODEWITHSUNDEEP
sql-serverdatabaset-sql
Ray
Ray

Reputation: 192406

How do you check what version of SQL Server for a database using TSQL?

Is there a system stored procedure to get the version #?

Upvotes: 131

Views: 243176

Answers (16)

Mark Kram
Mark Kram

Reputation: 5842

I know this is an older post but I updated the code found in the link (which is dead as of 2013-12-03) mentioned in the answer posted by Matt Rogish:

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

IF ( @ver = '7' )
   SELECT 'SQL Server 7'
ELSE IF ( @ver = '8' )
   SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
   SELECT 'SQL Server 2005'
ELSE IF ( @ver = '10' )
   SELECT 'SQL Server 2008/2008 R2'
ELSE IF ( @ver = '11' )
   SELECT 'SQL Server 2012'
ELSE IF ( @ver = '12' )
   SELECT 'SQL Server 2014'
ELSE IF ( @ver = '13' )
   SELECT 'SQL Server 2016'
ELSE IF ( @ver = '14' )
   SELECT 'SQL Server 2017'
ELSE
   SELECT 'Unsupported SQL Server Version'

Upvotes: 30

pruthvi
pruthvi

Reputation: 1

Try this:

SELECT
    'the sqlserver is ' + substring(@@VERSION, 21, 5) AS [sql version]

Upvotes: -1

Vikrant Bagal
Vikrant Bagal

Reputation: 11

select substring(@@version,0,charindex(convert(varchar,SERVERPROPERTY('productversion')) ,@@version)+len(convert(varchar,SERVERPROPERTY('productversion'))))

Upvotes: 1

Joe Kuemerle
Joe Kuemerle

Reputation: 6364

Try 

SELECT @@VERSION

or for SQL Server 2000 and above the following is easier to parse :) 

SELECT SERVERPROPERTY('productversion')
     , SERVERPROPERTY('productlevel')
     , SERVERPROPERTY('edition')

From: http://support.microsoft.com/kb/321185

Upvotes: 238

VAV
VAV

Reputation: 1896

Try

SELECT @@MICROSOFTVERSION / 0x01000000 AS MajorVersionNumber

For more information see: Querying for version/edition info

Upvotes: 2

Arif
Arif

Reputation: 1

Try this:

SELECT @@VERSION[server], SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Upvotes: 0

Allen Ackerman
Allen Ackerman

Reputation: 11

If all you want is the major version for T-SQL reasons, the following gives you the year of the SQL Server version for 2000 or later.

SELECT left(ltrim(replace(@@Version,'Microsoft SQL Server','')),4)

This code gracefully handles the extra spaces and tabs for various versions of SQL Server.

Upvotes: 1

Geoff
Geoff

Reputation: 8868

For SQL Server 2000 and above, I prefer the following parsing of Joe's answer:

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

Gives results as follows:

Result   Server Version
8.00     SQL 2000
9.00     SQL 2005
10.00    SQL 2008
10.50    SQL 2008R2
11.00    SQL 2012
12.00    SQL 2014

Basic list of version numbers here, or exhaustive list from Microsoft here.

Upvotes: 14

Nux
Nux

Reputation: 10042

Getting only the major SQL Server version in a single select:

SELECT  SUBSTRING(ver, 1, CHARINDEX('.', ver) - 1)
FROM (SELECT CAST(serverproperty('ProductVersion') AS nvarchar) ver) as t

Returns 8 for SQL 2000, 9 for SQL 2005 and so on (tested up to 2012).

Upvotes: 1

Alex
Alex

Reputation: 423

CREATE FUNCTION dbo.UFN_GET_SQL_SEVER_VERSION 
(
)
RETURNS sysname
AS
BEGIN
    DECLARE @ServerVersion sysname, @ProductVersion sysname, @ProductLevel sysname, @Edition sysname;

    SELECT @ProductVersion = CONVERT(sysname, SERVERPROPERTY('ProductVersion')), 
           @ProductLevel = CONVERT(sysname, SERVERPROPERTY('ProductLevel')),
           @Edition = CONVERT(sysname, SERVERPROPERTY ('Edition'));
    --see: http://support2.microsoft.com/kb/321185
    SELECT @ServerVersion = 
        CASE 
            WHEN @ProductVersion LIKE '8.00.%' THEN 'Microsoft SQL Server 2000'
            WHEN @ProductVersion LIKE '9.00.%' THEN 'Microsoft SQL Server 2005'
            WHEN @ProductVersion LIKE '10.00.%' THEN 'Microsoft SQL Server 2008'
            WHEN @ProductVersion LIKE '10.50.%' THEN 'Microsoft SQL Server 2008 R2'
            WHEN @ProductVersion LIKE '11.0%' THEN 'Microsoft SQL Server 2012'
            WHEN @ProductVersion LIKE '12.0%' THEN 'Microsoft SQL Server 2014'
        END

    RETURN @ServerVersion + N' ('+@ProductLevel + N'), ' + @Edition + ' - ' + @ProductVersion;

END
GO

Upvotes: 4

Zia
Zia

Reputation: 41

There is another extended Stored Procedure which can be used to see the Version info:

exec [master].sys.[xp_msver]

Upvotes: 4

crosswalk
crosswalk

Reputation: 21

SELECT 
@@SERVERNAME AS ServerName,
CASE WHEN LEFT(CAST(serverproperty('productversion') as char), 1) = 9 THEN '2005'
 WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 10 THEN '2008'
 WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 11 THEN '2012'
END AS MajorVersion,
SERVERPROPERTY ('productlevel') AS MinorVersion, 
SERVERPROPERTY('productversion') AS FullVersion, 
SERVERPROPERTY ('edition') AS Edition

Upvotes: 1

freak
freak

Reputation: 11

Try this:

if (SELECT LEFT(CAST(SERVERPROPERTY('productversion') as varchar), 2)) = '10'
BEGIN

Upvotes: 1

Bruce Chapman
Bruce Chapman

Reputation: 1247

Here's a bit of script I use for testing if a server is 2005 or later

declare @isSqlServer2005 bit
select @isSqlServer2005 = case when CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 9 then 0 else 1 end
select @isSqlServer2005

Note : updated from original answer (see comment)

Upvotes: 3

Matt
Matt

Reputation: 5172

The KB article linked in Joe's post is great for determining which service packs have been installed for any version. Along those same lines, this KB article maps version numbers to specific hotfixes and cumulative updates, but it only applies to SQL05 SP2 and up.

Upvotes: 2

Brannon
Brannon

Reputation: 26109

SELECT @@VERSION

Upvotes: 31

Related Questions