SQL Server Portal

“How to find the SQL Server port number ?” has been an old topic being discussed in multiple forums. The common solution provided is using xp_instance_regread. It is one of the useful undocumented stored procedures when you need to read registry settings. In this article, I will give you two solutions. First one using xp_instance_regread and second, using sys.dm_server_registry, a new dynamic management view shipped in SQL Server 2008 R2

Method 1 : Using xp_instance_regread.
In this method we will use xp_instance_regread to find the port number.

--This script will work on SQL Server 2005 and above
DECLARE @HkeyLocal nvarchar(18)
Declare @Instance varchar(100)
DECLARE @MSSqlServerRegPath nvarchar(200)
DECLARE @PortNumber nvarchar(100)

SET @Instance ='MSSQL11.MSSQLSERVER'
SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'
+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

Print @MSSqlServerRegPath
EXEC xp_instance_regread @HkeyLocal
, @MSSqlServerRegPath
, N'TcpPort'
, @PortNumber OUTPUT
SELECT @PortNumber as [Port Number]
--OUTPUT

Port Number
————
1433

(1 row(s) affected)

Method 2 : Using using sys.dm_server_registry.
In this method we will use sys.dm_server_registry to find the port number.

--This script will work on SQL Server 2012 and above
Use master
GO
Select value_data as [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
value_name ='TcpPort'

Port Number
————
1433

(1 row(s) affected)

Conclusion :
If you compare both methods, you will feel the difference, because the code is much optimized in the second method. Also, you can utilize the result set of second method further without using any temporary table but on the other hand you need temporary table to utilize the result set further.