Darren Myher's Blog

Determining the SQL Server Version is a relatively easy thing to do, but one thing I’ve always found frustrating is that there’s no command to return the version number in the year format that you see in marketing materials.

You can, for example, run SELECT SERVERPROPERTY(‘productversion’) as ‘productversion’ but that returns a number like 11.0.3128.0 – not “Microsoft SQL Server 2012 (SP1)” which is a lot easier for a person to read.

Select @@Version DOES return the name, but it also returns all sorts of other stuff you don’t necessarily want.

Fortunately, with a little bit of SQL parsing you can return just the part you want by taking advantage of the fact that @@Version happens to place a dash – character between the name in human form and the version number.

So to select just the version in year format, use this command:

SELECT SUBSTRING( @@Version, 1, charindex(‘-‘, @@Version) – 2) as ‘ProductVersionName’

Want most of these fields parsed out?  This is the command I use when gathering SQL stats across a whole group of servers:

SELECT
  @@SERVERNAME as ‘@@SERVERNAME’ 

— This value sometimes will not work
–(if server has been renamed for example) SOOO use the next statement instead: 
  , SERVERPROPERTY(‘servername’) as ‘servername’
  , SERVERPROPERTY(‘productversion’) as ‘productversion’
  , SERVERPROPERTY (‘productlevel’) as ‘productlevel’
  , SERVERPROPERTY (‘edition’) as ‘edition’
  , isnull(SERVERPROPERTY (‘InstanceName’),‘Default’) as ‘InstanceName’
  , SERVERPROPERTY(‘collation’) as ‘Collation’
  , SUBSTRING( @@Version, 1, charindex(‘-‘, @@Version) – 2) as ‘ProductVersionName’

Use this command with Toad for SQL Server 6 Group Execute feature and you’ve got a great way to pull all the versions of all the SQL Servers and instance names and collation etc from a whole group of servers: