1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

declare @Server as varchar(128) 
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512) 
declare @PortNumber as varchar(8) 

set @Server = @@ServerName 
set @Version = left(@@Version, 38) 
set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

if charindex('\',@@ServerName) > 0
begin
set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName)) 
set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
end

exec xp_regread 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = @KeyToInterogate, 
@value_name = 'TcpPort', 
@value = @PortNumber output 

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'ProductName'

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'CSDVersion'
                    
SELECT @@servername AS hostname
, SERVERPROPERTY('Edition') AS Edition
, ISNULL(SERVERPROPERTY('InstanceName'),'') AS InstanceName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') as ProductLevel 
, @@VERSION
, CASE 
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '8.0%' THEN '2000'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '9.0%' THEN '2005'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN '2008'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN '2008 R2'
END
, cast(@PortNumber as varchar)
, (select top 1 local_net_address from sys.dm_exec_connections
where local_net_address is not null) as IP_address

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;