Get the instances port number directly from code

This little code snippet lists the ports that sql server is listening on. It is working on sql 2000, 2005 and 2008. It give the port number for the current instance. It gives the ip address as well as the name of the viruals erver and the instance name. Works for both clusters and for stand alone servers. For sql 2005 and 2008 it also gives the statis and/or the dynamic port. Note, if the dynaic port is 0 this means that the server will listen to different ports each time the instance is restarted! Note, sinc ethe code is using regread and xp_cmdshell we still need to have the right sand cmdshell needs to be enabled. If we just need to know the port, we can use the code below for that.

declare @version as varchar(10)
Declare @staticport varchar(4)
declare @DynaicPort varchar(4)
declare @TmpString varchar(100)
Declare @ip as varchar(20)
DECLARE @SERVERNAME varchar(100)
set @version =(select cast(serverproperty('ProductVersion') as varchar(10)))
if left(@version,1) ='8'
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\', N'TcpPort',@staticport out
end
else
if cast(left(@version,1)as integer) >8
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpPort',@staticport out
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpDynamicPorts',@dynaicport out
end
set @TmpString =patindex('%\%',@@servername)
IF @TmpString =0
BEGIN
set @SERVERNAME = @@servername
END
if @TmpString >0
begin
set @SERVERNAME = left(@@servername,patindex('%\%',@@servername)-1)
end
DECLARE @ReturnCode int
set @TmpString = 'nslookup ' + @SERVERNAME
CREATE TABLE #Messages(Message nvarchar(400),counter int identity)
INSERT INTO #Messages
EXEC @ReturnCode = master..xp_cmdshell @TmpString
set @ip=(select top 1 right(message,15) from #Messages where message like'address:%'
order by counter desc)
DROP TABLE #Messages

select @SERVERNAME as ServerName,serverproperty('instancename')as Instance_name,@ip as IP_address,@staticport as Static_port,@dynaicport as Dynaic_port


This code is for only the ports:

eclare @version as varchar(10)
Declare @staticport varchar(4)
declare @DynaicPort varchar(4)
declare @TmpString varchar(100)
DECLARE @SERVERNAME varchar(100)
set @version =(select cast(serverproperty('ProductVersion') as varchar(10)))
if left(@version,1) ='8'
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\', N'TcpPort',@staticport out
end
else
if cast(left(@version,1)as integer) >8
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpPort',@staticport out
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpDynamicPorts',@dynaicport out
end
set @TmpString =patindex('%\%',@@servername)
IF @TmpString =0
BEGIN
set @SERVERNAME = @@servername
END
if @TmpString >0
begin
set @SERVERNAME = left(@@servername,patindex('%\%',@@servername)-1)
end


select @SERVERNAME as ServerName,serverproperty('instancename')as Instance_name,@staticport as Static_port,@dynaicport as Dynaic_port