Get SQL Server port number with TSQL

Normally you’d go through SQL Server Configuration Manager and look at the TCP/IP settings but if you want to use just TSQL then try this

DECLARE @InstName VARCHAR(16)
DECLARE @RegLoc VARCHAR(100)
SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

Leave a Reply

Your email address will not be published. Required fields are marked *