Get Server version Info

  • Comments posted to this topic are about the item Get Server version Info

  • Thanx for the great code and examples.

    I only made one change and that was to add the following line to the code

    WHEN '6.3' THEN 'Windows Server 2012 R2'

  • Thanks for the update, I will include your changes to the code an update it.

  • I found it really difficult to assemble a chart of versions like this for my own use. Thanks for thinking to share it. Mine is a much messier version that runs master.dbo.xp_msver to get the information.

    If you want to go backwards (for poor souls stuck on a legacy unsupported versions, I had to support a SQL Server 6.5 on Windows NT for a legacy app just a few years ago):

    WHEN '3.51' THEN 'Windows NT 3.51'

    WHEN '4.0' THEN 'Windows NT'

    WHEN '5.0' THEN 'Windows 2000'

    ...

    6.0 can be Vista as well as Server 2008, which of course is unlikely, but... and it seems like Windows 10 will be 10.? (they used 10.0 for technical preview 4) but I might be getting ahead of myself!

    I think SERVERPROPERTY only works on SQL Server 2008 or later, so maybe some of those old versions can never reasonably happen...?

  • Thanks! Very helpful.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thanks. I have verified ServerProperty does work as far back as SQL Server 2000. Couldn't really find a way to differentiate between server and non-server versions so just included both. Note: Windows Server 2012 R2 Datacenter actually returns the name in @@Version; it does NOT give the generic Windows NT at all.

    Here's my take on it:

    SELECT@@SERVERNAME + ': ' + RTRIM(LEFT(@@version, CHARINDEX('-', @@version, 1) - 2)) + ' ' + CAST(SERVERPROPERTY('Edition') AS varchar(128)) + ' '

    + +CAST(SERVERPROPERTY('ProductLevel') AS varchar(128)) + ' ' + CAST(SERVERPROPERTY('ProductVersion') AS varchar(128)) + ' '

    + CASE WHEN CHARINDEX('Windows NT', @@VERSION) > 0 THEN 'on ' + CASE RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)

    WHEN '5.0' THEN 'Windows 2000'

    WHEN '5.1' THEN 'Windows Server 2003/Windows XP'

    WHEN '5.2' THEN 'Windows Server 2003 R2/Windows XP 64-Bit Edition'

    WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'

    WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'

    WHEN '6.2' THEN 'Windows Server 2012/Windows 8'

    WHEN '6.3' THEN 'Windows Server 2012 R2/Windows 8.1'

    ELSE 'UnKnown Windows OS Type'

    END + ' ' + SUBSTRING(@@VERSION, CHARINDEX('(Build', @@VERSION), 512)

    ELSE SUBSTRING(@@VERSION, CHARINDEX('on Windows', @@VERSION), 512)

    END

    some sample outputs with server name masked:

    xxx: Microsoft SQL Server 2000 Standard Edition SP4 8.00.2066 on Windows Server 2003 R2/Windows XP 64-Bit Edition (Build 3790: Service Pack 2)

    xxx: Microsoft SQL Server 2005 Enterprise Edition SP4 9.00.5266.00 on Windows Server 2008 R2/Windows 7 (Build 7601: Service Pack 1)

    xxx: Microsoft SQL Server 2012 Enterprise Edition (64-bit) SP2 11.0.5343.0 on Windows Server 2008 R2/Windows 7 (Build 7601: Service Pack 1) (Hypervisor)

    xxx: Microsoft SQL Server 2014 Developer Edition (64-bit) SP1 12.0.4100.1 on Windows Server 2012 R2/Windows 8.1 (Build 9600: ) (Hypervisor)

    xxx: Microsoft SQL Server 2016 (CTP2.3) Enterprise Evaluation Edition (64-bit) CTP 13.0.500.53 on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

  • Thanks for the script. It worked perfectly on our system.

  • select @@VERSION

    Igor Micev,
    My blog: www.igormicev.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply