http://www.sqlservercentral.com/Forums/Topic912647-2621-1.aspx
DECLARE @Server AS VARCHAR(128)
DECLARE @KeyToInterogate AS VARCHAR(200)
DECLARE @PortNumber AS VARCHAR(8)
SET @Server = @@SERVERNAME
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
SELECT @@SERVERNAME AS hostname
, Serverproperty(‘Edition’) AS edition
, Isnull(Serverproperty(‘InstanceName’),”) AS instancename
, Serverproperty(‘MachineName’) AS machinename
, Serverproperty(‘ProductVersion’) AS productversion
, 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) AS portno
| Simon Cho |
