mssql instance 동적 할당 된 port 확인하기

[출처] http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/

set nocount on
DECLARE @test varchar(20), @key varchar(100)
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=@test OUTPUT
SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@test)

[샘플]

set nocount on

DECLARE @test varchar(20), @key varchar(100), @servername varchar(255)

 

set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL11.TEST6\MSSQLServer\Supersocketnetlib\TCP\IPAll'

 

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@key,@value_name='TcpDynamicPorts',@value=@test OUTPUT

SELECT convert(varchar(10),@test)