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

Question

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

http://blog.devstone.com/aaron/default,date,2006-12-15.aspx – Matt Rogish Sep 12 '08 at 16:31 — Matt Rogish, Sep 12 '08 at 16:31

score 238 · Accepted Answer · edited Jul 13 '17 at 01:23

238

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

edited Jul 13 '17 at 01:23

Jake

604
3
9
33

answered Sep 12 '08 at 16:33

Joe Kuemerle

6,338
1
23
18

1

The second one works for me, and i add to check on Wikipedia to understand that 8.00.xx means SQL server 2000 – pdem Apr 28 '15 at 07:52

score 31 · Answer 2 · answered Sep 12 '08 at 16:30

31

SELECT @@VERSION

answered Sep 12 '08 at 16:30

Brannon

25,687
5
39
44

score 29 · Answer 3 · edited Aug 30 '18 at 16:36

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'

Geoff · Answer 4 · 2015-06-01T04:35:57.713

14

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.

edited Jun 01 '15 at 04:35

answered Jun 23 '11 at 13:31

Geoff

8,551
1
43
50

I like this, nice, simple and resusable across server versions. I used a slightly modified version of the above: `select cast(serverproperty('productversion') as varchar) as [result]`. My point is that I can execute the above via ADO.NET's `ExecuteScalar` and then parse the result string as a `System.Version` object. Also, conveting it into numeric gives different meanings to version numbers when it comes to trailing zeros and version segment digit count, while a string can be parsed to a valid `Version` object without losing the consistency of each version component. – Ivaylo Slavov Apr 24 '15 at 08:05

score 4 · Answer 5 · edited Aug 07 '14 at 17:51

4

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

exec [master].sys.[xp_msver]

edited Aug 07 '14 at 17:51

Palec

12,743
8
69
138

answered Aug 07 '14 at 17:27

Zia

41
1

score 4 · Answer 6 · answered Nov 08 '14 at 15:02

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

Bruce Chapman · Answer 7 · 2011-07-07T09:39:49.777

3

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)

edited Jul 07 '11 at 09:39

answered Feb 04 '09 at 09:59

Bruce Chapman

1,227
3
12
19

Just found this doesn't work sql 2008 because '10' is less than '9'. You can change the value in the updated answer to use 8, 9, 10 or whatever you need to test as a minimum value – Bruce Chapman Jun 29 '11 at 07:01

score 2 · Answer 8 · edited May 23 '17 at 11:33

2

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.

edited May 23 '17 at 11:33

Community

1
1

answered Sep 13 '08 at 08:00

Matt

5,052
4
36
54

score 1 · Answer 9 · answered May 15 '13 at 17: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

score 1 · Answer 10 · edited Apr 24 '15 at 08:10

1

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).

edited Apr 24 '15 at 08:10

Ivaylo Slavov

8,839
12
65
108

answered Apr 30 '14 at 10:32

Nux

9,276
5
59
72

score 1 · Answer 11 · edited Nov 19 '11 at 20:40

1

Try this:

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

edited Nov 19 '11 at 20:40

animuson

53,861
28
137
147

answered Jul 05 '10 at 11:05

freak

11
1

score 1 · Answer 12 · edited Oct 21 '16 at 14:11

1

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.

edited Oct 21 '16 at 14:11

user489998

4,473
2
29
35

answered Oct 21 '16 at 13:12

Allen Ackerman

11
2

score 1 · Answer 13 · answered May 03 '17 at 18:13

1

Try

SELECT @@MICROSOFTVERSION / 0x01000000 AS MajorVersionNumber

For more information see: Querying for version/edition info

answered May 03 '17 at 18:13

VAV

1,756
1
16
26

score 1 · Answer 14 · answered Mar 27 '18 at 12:23

1

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

answered Mar 27 '18 at 12:23

Vikrant Bagal

11
6

score 0 · Answer 15 · edited Feb 17 '17 at 07:54

0

Try this:

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

edited Feb 17 '17 at 07:54

malioboro

3,097
4
35
55

answered Feb 17 '17 at 07:26

Arif

1
1

score -1 · Answer 16 · edited Jun 13 '18 at 08:21

-1

Try this:

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

edited Jun 13 '18 at 08:21

MAXE

4,978
2
45
61

answered Mar 04 '16 at 02:46

pruthvi

1

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

16 Answers16

Linked