--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..x
--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 go --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
