CODEWITHSUNDEEP
sqlsql-server
VladL
VladL

Reputation: 13043

Get user friendly MSSQL server's product name

I'm looking for a way to get the user friendly MSSQL product name.

I've tried:

select @@version

but it returns to much information (I don't want to parse it now)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Another try was

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

which returns 10.50.1617.0 RTM Developer Edition (64-bit)

I tried to get the SERVERPROPERTY for every property from this list, but couldn't find the needed one.

Is there a way to get the string Microsoft SQL Server 2008 R2 only?

Thanks

Upvotes: 2

Views: 3745

Answers (3)

gvee
gvee

Reputation: 17171

Ok, so how about this then... It's not a wholly complete answer but I'm putting it out there as something for people to toy with.

Obviously not an exhaustive list so isn't future proof but gives you something to work with. I've included the many references I've used within the code itself:

SELECT 'Microsoft SQL Server '
     + CASE ParseName(Cast(ServerProperty('ProductVersion') As varchar(100)), 4)
         /* This function did not exist in versions prior to 2005
         WHEN  6 THEN '6' -- there is a v6.5 but we'll let that return "6 R2"
         WHEN  7 THEN '7.0'
         WHEN  8 THEN '2000'
         */
         -- How to determine the version and edition of SQL Server: http://support.microsoft.com/kb/321185
         WHEN  3 THEN 'Mobile/Compact Edition'
         WHEN  9 THEN '2005'
         WHEN 10 THEN '2008'
         WHEN 11 THEN '2012'
         WHEN 12 THEN '2014' -- http://social.msdn.microsoft.com/Forums/en-US/199f43df-243e-4473-a765-e0e45c0ca3d5/sql-server-2014-release-date-and-version-number
         ELSE '(unknown)'
       END
     + CASE ParseName(Cast(ServerProperty('ProductVersion') As varchar(100)), 3)
         WHEN 50 THEN ' R2'    -- 
         WHEN 25 THEN ' Azure' -- http://msdn.microsoft.com/en-us/library/ff951630.aspx
         WHEN  5 THEN ' 3.5'   -- CE https://blogs.msdn.com/b/sqlservercompact/archive/2008/02/08/sql-server-compact-release-versions.aspx
         ELSE ''
       END As sql_version

Upvotes: 0

Kaf
Kaf

Reputation: 33849

Try this (if you need to remove (RTM)):

 select 
     case when charindex('-', @@version,0) < charindex('(', @@version,0)
          then left(@@version, charindex('-', @@version,0)-1)
     else left(@@version, charindex('(', @@version,0)-1) end as myserver

 --Results 
 Microsoft SQL Server 2008 R2
 Microsoft SQL Server 2012

Else 

 select left(@@version, charindex('-', @@version,0)-1) as myserver

 --Results 
 Microsoft SQL Server 2008 R2 (RTM)
 Microsoft SQL Server 2012

Fiddle demo

Upvotes: 2

peterm
peterm

Reputation: 92815

How about

SELECT LEFT(@@version, CHARINDEX(' - ', @@version)) ProductName;

Note: you can obviously adjust it to your needs (like trim RTM if you have to etc.)

Sample output SQL Server 2008:

|                         PRODUCTNAME |
|-------------------------------------|
| Microsoft SQL Server 2008 R2 (RTM)  |

Here is SQLFiddle demo

Sample output SQL Server 2012:

|                PRODUCTNAME |
|----------------------------|
| Microsoft SQL Server 2012  |

Here is SQLFiddle demo

Upvotes: 6

Related Questions