Script to find port number of a SQL Server instance in Multiple ways

--Method 1
DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
 ,@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
 ,@value_name = 'TcpPort'
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port

--Method 2

sp_readerrorlog 1
 ,1
 ,'listening'
 ,'server'

--Method 3
SELECT SERVERNAME = CONVERT(NVARCHAR(128), SERVERPROPERTY('SERVERNAME'))
 ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
 ,local_tcp_port AS 'PortNumber'
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID

Script to list all SQL Server instance names in a server

SET NOCOUNT ON

DECLARE @CurrID INT
 ,@ExistValue INT
 ,@MaxID INT
 ,@SQL NVARCHAR(1000)
DECLARE @TCPPorts TABLE (
 PortType NVARCHAR(180)
 ,Port INT
 )
DECLARE @SQLInstances TABLE (
 InstanceID INT identity(1, 1) NOT NULL PRIMARY KEY
 ,InstName NVARCHAR(180)
 ,Folder NVARCHAR(50)
 ,StaticPort INT NULL
 ,DynamicPort INT NULL
 ,Platform INT NULL
 );
DECLARE @Plat TABLE (
 Id INT
 ,NAME VARCHAR(180)
 ,InternalValue VARCHAR(50)
 ,Charactervalue VARCHAR(50)
 )
DECLARE @Platform VARCHAR(100)

INSERT INTO @Plat
EXEC xp_msver platform

SELECT @Platform = (
  SELECT 1
  FROM @plat
  WHERE charactervalue LIKE '%86%'
  )

IF @Platform IS NULL
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 64
END
ELSE
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 32
END

DECLARE @Keyexist TABLE (Keyexist INT)

INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT @ExistValue = Keyexist
FROM @Keyexist

IF @ExistValue = 1
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

UPDATE @SQLInstances
SET Platform = 32
WHERE Platform IS NULL

SELECT @MaxID = MAX(InstanceID)
 ,@CurrID = 1
FROM @SQLInstances

WHILE @CurrID <= @MaxID
BEGIN
 DELETE
 FROM @TCPPorts

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 UPDATE SI
 SET StaticPort = P.Port
  ,DynamicPort = DP.Port
 FROM @SQLInstances SI
 INNER JOIN @TCPPorts DP ON DP.PortType = 'TCPDynamicPorts'
 INNER JOIN @TCPPorts P ON P.PortType = 'TCPPort'
 WHERE InstanceID = @CurrID;

 SET @CurrID = @CurrID + 1
END

SELECT serverproperty('ComputerNamePhysicalNetBIOS') AS ServerName
 ,InstName
 ,StaticPort
 ,DynamicPort
 ,Platform
FROM @SQLInstances

SET NOCOUNT OFF

Finding the Port Number for a particular SQL Server Instance

DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = ‘HKEY_LOCAL_MACHINE’
 ,@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’
 ,@value_name = ‘TcpPort’
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port