Parse @@VERSION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Parse @@VERSION

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-07 : 16:48:07

This script parses the @@VERSION global variable into individual columns.

I developed it because I wanted to be able gather standard info on all versions.

I know there are functions for a lot of this, but only starting with SQL 2000.

It seems to work with all versions of SQL Server from 7.0 through 2005.

I haven't tested with 6.5 and before or 2008, because I don't have either available.

Please report any problems you see.

Edit: 2007-7-31
1. Changed SQL_SERVER_MAJOR_VERSION to varchar(20)
2. Added code to create a view named V_SQL_SERVER_VERSION
3. Added four new columns to the view:
SERVER_NAME, value from @@servername
SQL_SERVER_MAJOR_VERSION_NUMBER, Example: 9
SQL_SERVER_VERSION_NUMBER, Example: 8.0020390000
WINDOWS_VERSION_NAME, Example: 'Windows 2000'

Edit: 2007-8-2
Changed SQL_SERVER_MAJOR_VERSION to varchar(40)


select
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
	SQL_SERVER_VERSION =
		convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
	SQL_SERVER_PLATFORM =
		convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
	SQL_SERVER_EDITION =
		convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
	WINDOWS_VERSION =
		convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
	WINDOWS_BUILD =
		convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
	WINDOWS_SERVICE_PACK =
		convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2)))
from
(
select
	L1_BREAK_1 = charindex(' - ',L1),
	L1_BREAK_2 = charindex(' (',L1),
	L1_BREAK_3 = charindex(')',L1),
	L4_BREAK_1 = charindex(' on Windows',L4),
	L4_BREAK_2 = charindex(' (',L4),
	L4_BREAK_3 = charindex(': ',L4),
	L4_BREAK_4 = charindex(')',L4),
	L1,
	L4
from
(
select
	L1 =
	convert(varchar(100),
	rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
	) ,
	L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))

from
(
select
	zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(

select
	yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
	xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
(
select VERSION = @@VERSION 
) a ) a1 ) a2 ) a3 ) a4 ) a4

Results:


SQL_SERVER_MAJOR_VERSION SQL_SERVER_VERSION   SQL_SERVER_PLATFORM  SQL_SERVER_EDITION             WINDOWS_VERSION      WINDOWS_BUILD        WINDOWS_SERVICE_PACK           
------------------------ -------------------- -------------------- ------------------------------ -------------------- -------------------- ------------------------------ 
2000                     8.00.2039            Intel X86            Standard Edition               Windows NT 5.0       Build 2195           Service Pack 4

(1 row(s) affected)


drop view [dbo].[V_SQL_SERVER_VERSION]
go
create view [dbo].[V_SQL_SERVER_VERSION]
as
select
	SERVER_NAME = @@servername,
	SQL_SERVER_MAJOR_VERSION,
	SQL_SERVER_VERSION,
	SQL_SERVER_MAJOR_VERSION_NUMBER =
		convert(int,floor(convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4)))),
	SQL_SERVER_VERSION_NUMBER=
		convert(numeric(20,10),(
		convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4))*1000000+
		convert(numeric(20,10),substring(SQL_SERVER_VERSION,6,30)))/1000000),
	SQL_SERVER_PLATFORM,
	SQL_SERVER_EDITION,
	WINDOWS_VERSION_NAME =
		convert(varchar(20),
		case
		when WINDOWS_VERSION = 'Windows NT 5.0'
		then 'Windows 2000'
		when WINDOWS_VERSION = 'Windows NT 5.1'
		then 'Windows XP'
		when WINDOWS_VERSION = 'Windows NT 5.2'
		then 'Windows 2003'
		else WINDOWS_VERSION
		end),
	WINDOWS_VERSION,
	WINDOWS_BUILD,
	WINDOWS_SERVICE_PACK
from
(
select
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
	SQL_SERVER_VERSION =
		convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
	SQL_SERVER_PLATFORM =
		convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
	SQL_SERVER_EDITION =
		convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
	WINDOWS_VERSION =
		convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
	WINDOWS_BUILD =
		convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
	WINDOWS_SERVICE_PACK =
		convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))),
	VERSION = VERSION
from
(
select
	VERSION,
	L1_BREAK_1 = charindex(' - ',L1),
	L1_BREAK_2 = charindex(' (',L1),
	L1_BREAK_3 = charindex(')',L1),
	L4_BREAK_1 = charindex(' on Windows',L4),
	L4_BREAK_2 = charindex(' (',L4),
	L4_BREAK_3 = charindex(': ',L4),
	L4_BREAK_4 = charindex(')',L4),
	L1,
	L4
from
(
select
	VERSION,
	L1 =
	convert(varchar(100),
	rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
	) ,
	L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))

from
(
select
	VERSION,
	zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(

select
	VERSION,
	yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
	VERSION,
	xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
	( select VERSION = @@version ) a ) a1 ) a2 ) a3 ) a4 ) a4 ) a5
go
grant select on [dbo].[V_SQL_SERVER_VERSION] to public
go

select * from [dbo].[V_SQL_SERVER_VERSION]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-22 : 21:08:00

[code] SQL_SERVER_MAJOR_VERSION =
convert(varchar(10 20),substring(L1,1,L1_BREAK_1-1)), -- Increase from 10 to 20 for Katmai

SQL_SERVER_MAJOR_VERSION
------------------------
code name "Katmai"[/code]
Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-23 : 15:10:18

>> code name "Katmai"

Is that from the SQL Server 2008 CTP?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-23 : 16:41:36

Yes. The one released last monday.

select @@version returns

Microsoft SQL Server code name "Katmai" - 10.0.1019.17 (Intel X86) May 24 2007 15:26:55 Copyright (c) 1988-2007 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

It is only 18 characters, but since you have a string pointer anyway, I couldn't see the harm of having 20 characters, just in case.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 08:10:54

When are MS going to normalise this?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-25 : 08:15:40

>>When are MS going to normalise this?
There's xp_msver now too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-25 : 11:57:46

In SQL Server 2000 and later, this info is available from function calls. The reason I did this script is to be able to gather info from servers that have unknown versions, like SQL 7. I have located about 300+ servers on our network, and want a unified way to gather this info. Crude but it works.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 20:29:00

July CTP for SQL Server 2008

Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

The new thing is the CTP part.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-02 : 10:25:46

quote:
Originally posted by Peso

July CTP for SQL Server 2008
Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86)
Jul 25 2007 20:28:28
Copyright (c) 1988-2007 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
The new thing is the CTP part.

E 12°55'05.25"
N 56°04'39.16"

Can you see if the change I just made works with that version? I don't have 2008 installed.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 15:40:59

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 15:42:17

[code]declare @s VARCHAR(8000)
set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86)
Jul 25 2007 20:28:28
Copyright (c) 1988-2007 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'[/code]

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 16:22:02

Actually I got the same error when I ran it against my 2000 production server.


---
Microsoft SQL Server  2000 - 8.00.789 (Intel IA-64) 
	Mar 27 2003 19:20:49 
	Copyright (c) 1988-2003 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 18:04:21

quote:
Originally posted by Michael Valentine Jones

Can you see if the change I just made works with that version? I don't have 2008 installed.

No. Still same error.

declare @s VARCHAR(8000)
set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-22 : 16:52:36

[code]declare @s VARCHAR(8000)

set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1075.23 (Intel X86)
Nov 8 2007 14:16:26
Copyright (c) 1988-2007 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 16:34:02

[code]SELECT @@VERSION

Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64)
Jul 9 2009 23:46:07
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7100: )[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 06:02:39

[code]Microsoft SQL Server 2008 R2 (CTP) - 10.50.1092.20 (X64)
Jul 22 2009 21:22:48
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )[/code]

N 56°04'39.26"
E 12°55'05.63"

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2013-09-16 : 12:33:38

Sorry to reply to such an old topic, but I had a need for it today, and to be able to parse @@VERSION for newer versions of SQL Server (up to and including 2012), and Michaels brilliant code had a few shortcomings for newer versions (parentheses galore), and since this shows up in a Google search, I thought I better add my minor corrections.

select
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(40), substring(L1, 1, L1_BREAK_1 - 1)),
	SQL_SERVER_VERSION =
		convert(varchar(20), substring(L1, L1_BREAK_1 + 3, COALESCE(NULLIF(L1_BREAK_2, 0), LEN(L1) + 2) - (L1_BREAK_1 + 3))),
	SQL_SERVER_PLATFORM = CASE WHEN L1_BREAK_2 > 0 THEN
		convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))) END,
	SQL_SERVER_EDITION = CASE WHEN L4_BREAK_1 > 0 THEN
		convert(varchar(50),substring(L4,1,L4_BREAK_1-1)) END,
	WINDOWS_VERSION = CASE WHEN L4_BREAK_1 > 0 THEN
		convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))) END,
	WINDOWS_BUILD = CASE WHEN L4_BREAK_1 > 0 THEN
		convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))) END,
	WINDOWS_SERVICE_PACK = CASE WHEN L4_BREAK_1 > 0 THEN
		convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))) END
from (
select
	L1,
	L1_BREAK_1,
	L1_BREAK_2 = charindex(' (', L1, L1_BREAK_1),
	L1_BREAK_3 = charindex(')', L1, L1_BREAK_1),
	L4,
	L4_BREAK_1,
	L4_BREAK_2 = charindex(' (', L4, L4_BREAK_1),
	L4_BREAK_3 = charindex(': ', L4, L4_BREAK_1),
	L4_BREAK_4 = charindex(')', L4, L4_BREAK_1)
from (
select
	L1,
	L1_BREAK_1 = charindex(' - ',L1),
	L4,
	L4_BREAK_1 = charindex(' on Windows', L4)
from (
select
	L1 =
	convert(varchar(100),
	rtrim(ltrim(replace(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server',''), 'Microsoft SQL', '')))
	) ,
	L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))
from (
select
	zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from (
select
	yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from (
select
	xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from (
	select VERSION = @@VERSION
) a ) a ) a ) a ) a ) a ) a

EDIT 12/01/2014: Fixed size of SQL_SERVER_EDITION ('Enterprise Edition: Core-based Licensing (64-bit)' is very long!)

EDIT 04/06/2016: Azure

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources