Finding Port Number of SQL Instance

There are times when one needs a port number for SQL Server instance . finding port number through automated script is not that simple as the environment may have named instance and default instances. both types need different way to fetch out port number from registry key . i have wrote a script which help to automate this task. the script first finds the type of instance , whether it is default or named and then query respective keys to get the port number …

 

DECLARE @tcpport nvarchar(5)
DECLARE @insname  nvarchar(50)
DECLARE @MyKey  nvarchar(254)
if CHARINDEX(‘\’,@@servername) > 0
begin
set @insname = substring(@@servername,CHARINDEX(‘\’,@@servername)+ 1,len(@@servername))
set @MyKey = ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\’ + @insname + ‘\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
else
begin
set @MyKey = ‘SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
EXEC xp_regread
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
@key        =    @MyKey,
@value_name    =    ‘TcpPort’,
@value        =    @tcpport OUTPUT

Select @@servername as ServerName ,@tcpport as PortNumber

 

The query works fine with both Sql Server 2008 and 2005 instances