| 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 2008Microsoft 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 2008Microsoft 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. |
|
|
|
|
|
|
Parse @@VERSION