Hiren, @@version will give you a lot of information, but it is difficult to parse.
Getting information about the database instance is fairly easy: a combination of SERVERPROPERTY, sp_server_info and xp_msver. Any of these (or all of them) are much easier to parse if you want to do something based on the specific version of SQL or Windows that are being run.
DECLARE @dbms_ver TABLE (attribute_id int, attribute_name varchar(60), attribute_value varchar(255))
DECLARE @msver TABLE ([index] int, name varchar(255), internal_value int, character_value varchar(255))
DECLARE @msver_pvt TABLE ([WindowsVersion] varchar(255), [Platform] varchar(255))
INSERT INTO @dbms_ver(attribute_id, attribute_name, attribute_value)
EXEC sp_server_info 2
INSERT INTO @msver([index], [name], [internal_value], [character_value])
EXEC xp_msver 'WindowsVersion'
INSERT INTO @msver([index], [name], [internal_value], [character_value])
EXEC xp_msver 'Platform'
INSERT INTO @msver_pvt(WindowsVersion, [Platform])
SELECT
[WindowsVersion],
[Platform]
FROM
(
SELECT name, character_value FROM @msver
) base
PIVOT
(
MIN([character_value])
FOR [name] IN ([WindowsVersion],[Platform])
) pvt
SELECT
d.attribute_value as [sql version],
SERVERPROPERTY('Edition') as [edition],
SERVERPROPERTY('ProductVersion') as [product version],
SERVERPROPERTY('ProductLevel') as [product level],
m.WindowsVersion as [windows version],
m.Platform as [platform]
FROM
@dbms_ver d,
@msver_pvt m
commented on Jun 10 2012 7:39PM