有SQL语句可以获得数据库服务器IP的么？

 



1:得到客户端的IP地址

/************* IP **************/

declare @ip varchar(20),@hst varchar(20),@sql varchar(100)

declare @str varchar(100)

set @str='PING '+Host_Name()

create table #tmp(aa varchar(200))

insert #tmp exec master..xp_cmdshell @str

select top 1 @ip = replace(left(aa,charindex(':',aa)-1),'Reply from ','') 

from #tmp where aa like 'reply from %:%'

drop table #tmp

select @ip





2:得到网卡的物理地址

create table #tb(re varchar(255))

insert into #tb exec master..xp_cmdshell 'ipconfig /all'



select 网卡物理地址=substring(re,charindex(':',re)+1,255) from #tb where re like '%Physical Address. . . . . . . . . :%' 



drop table #tb



3: 将IP地址段转成每三位用点号分开 

create function getIP(@a varchar(15))

returns varchar(15)

As

begin

declare @s varchar(15)

set @s = ''

while charindex('.',@a) >0

begin

set @s = @s + right('000' + left(@a,charindex('.',@a)),4)

set @a = right(@a,len(@a)-charindex('.',@a))

end

set @s = @s + right('000' + @a,3)

return @s

end



/*

Select dbo.getIP('202.1.110.2')

--------------- 

202.001.110.002



（所影响的行数为 1 行）

*/

--drop function getIP 

create table #(Col nvarchar(100))

insert # exec xp_cmdshell 'ping 機器名 '



select top 1

	substring(Col,charindex('[',Col)+1,charindex(']',Col)-charindex('[',Col)-1)

from 

	#

where	

	len(COL)>1