본문 바로가기

프로그램 경험/Database

[SqlServer] 데이터베이스 서버 IP 주소 알아내기


You can get the[hostname]\[instancename] by:

SELECT @@SERVERNAME;

To get only the hostname when you have hostname\instance name format:

SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1)

Alternatively as @GilM pointed out:

SELECT SERVERPROPERTY('MachineName')

You can get the actual IP address using this:

create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
          set @ip = NULL
          Create table #temp (ipLine varchar(200))
          Insert #temp exec master..xp_cmdshell 'ipconfig'
          select @ipLine = ipLine
          from #temp
          where upper (ipLine) like '%IP ADDRESS%'
          if (isnull (@ipLine,'***') != '***')
          begin 
                set @pos = CharIndex (':',@ipLine,1);
                set @ip = rtrim(ltrim(substring (@ipLine , 
               @pos + 1 ,
                len (@ipLine) - @pos)))
           end 
drop table #temp
set nocount off
end 
go

declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip

Source of the SQL script.

출처 : http://stackoverflow.com/questions/142142/sql-query-to-get-servers-ip-address