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)

4 comments:

  1. I liked this article very much. The content is very good. Keep posting.

    SQL Server Online Training

    ReplyDelete
  2. Hi,

    When i excute above query i am getting below error could you please provide the solution,

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'BEGINSET'.

    ReplyDelete
    Replies
    1. 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..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
      @key = @key,
      @value_name = 'Tcpport',
      @value = @port OUTPUT;

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

      Delete
  3. --Query to check TCP port Instance is listening to
    SELECT DISTINCT
    local_tcp_port
    FROM sys.dm_exec_connections
    WHERE local_tcp_port IS NOT NULL;

    ReplyDelete