Check SQL Server Port Number using T-SQL Scripts

How to check SQL Server Port Number using T-SQL Scripts

It could be difficult to find the port used by the database engine if you have multiple instances of SQL Server running on the same server or if you have modified the default port.

 

How can I check which TCP/IP port a given SQL Server instance is running on? In this tip, we'll discuss different methods a database administrator can use to identify the port a SQL Server instance is using.

 

Below script is quite helpful to DBAs when managing many SQL instances.

SET NOCOUNT ON

 

DECLARE

            @Port VARCHAR(30)

            ,@Key VARCHAR(150)

IF CHARINDEX('\',@@SERVERNAME,0) <>0

BEGIN

            SET @Key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'

                                                +@@SERVICENAME+'\MSSQLServer\Supersocketnetlib\TCP'

END

ELSE

BEGIN

            SET @Key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'

END

EXEC master.sys.xp_regread

            @Rootkey='HKEY_LOCAL_MACHINE'

            ,@Key=@Key,@value_name='Tcpport'

            ,@value=@Port OUTPUT

 

SELECT 'SQL Server Name: '+@@SERVERNAME + ' Port # '+CONVERT(VARCHAR(20),@Port)

 

 Result: