Though this may be trivial it is always useful. There are many ways to determine what version, edition and service pack level of SQL Server you are running. So here is a one way to get the information you need.
SELECT RIGHT(LEFT(@@VERSION,25),15) AS [Product] ,SERVERPROPERTY('productversion') AS [Version] ,SERVERPROPERTY('edition') AS [Edition] ,SERVERPROPERTY('productlevel') AS [Service Pack] ,SERVERPROPERTY('ServerName') AS [ServerName]
If the char length of SQL Server 2008 changes then (line 1) will need to be adjusted accordingly. I haven’t searched for a better solution; however, if you know of one please feel free to pass on the information. I would appreciate it.
Recently as I was working more with CHARINDEX I thought I might use to extract the product from @@VERSION. The number 15 is the char length of SQL Server 2008. Hence the 15 of course.
SELECT SUBSTRING(@@VERSION,CHARINDEX('SQL',@@VERSION),15) 'Product'
It will return SQL Server 2008. Again if the product name changes CHAR length then this will be off.