DBA Posts - How to find SQL Server Port number using T-SQL?


How to find SQL Server Port Number from Management Studio?


Sometimes it is easier to run a query to find SQL Server Port number than having to remote into the server - You can find SQL Server Port number using below T-SQL query.

SET NOCOUNT ON
DECLARE @port varchar(30), @key varchar(150)
IF charindex('\',@@servername,0) <>0
BEGINSET @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGIN
SET @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'
ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,@value_name='Tcpport',@value=@port OUTPUT

SELECT 'SQL Server Name: '+@@servername + ' Port # '+convert(varchar(20),@port)