Step 1
DECLARE @tcp_port nvarchar(5)
if @@SERVICENAME = 'MSSQLSERVER'
BEGIN
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
END
ELSE
BEGIN
declare @keyname nvarchar(500)
set @keyname = '' + 'Software\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + N'\MSSQLServer\SuperSocketNetLib\TCP'
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @keyname,
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
END
select @tcp_port
Step 2
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
and net_transport = 'TCP'
