===========================================================
mssql根据对方ip地址取得主机名的proc
===========================================================
作者: kyle(http://kyle.itpub.net)
发表于: 2004.12.09 22:24
分类: dbms
出处: http://kyle.itpub.net/post/1626/9270
---------------------------------------------------------------
发表于: 2004.12.09 22:24
分类: dbms
出处: http://kyle.itpub.net/post/1626/9270
---------------------------------------------------------------
--欲得到结果执行exec gethostname 'ip_address'
--drop proc gethostname
create procedure Gethostname
(@gethost varchar(30))
as
begin
create table #m(demo varchar(500))
declare @sql varchar(99),@b int,@c varchar(50)
select @c='master..xp_cmdshell '+''' tracert '''
select @b=charindex('rt',@c,1)
select @sql=stuff(@c,@b+3,0,@gethost)--取得类似master..xp_cmdshell 'tracert target'的效果
insert into #m exec(@sql)
declare @s varchar(99),@ip varchar(24),@p int,@l int,@a int
select @s=rtrim(ltrim(demo)) from #m where demo like '%tracing%'
select @l=len(@s),@p=charindex('to',@s,2) ,@a=charindex('[',@s,1)
select @ip=rtrim(ltrim(substring(@s,@p+2,(@a-@p-2))))
select @ip as '主机名'
drop table #m
end
