熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

用T-SQL獲得當前連接客戶端IP和機器名

2013-11-15 14:36:31  來源: SQL Server 

  Create proc usp_getClient_infor
  as
  set nocount on
  
  Declare @rc int
  Declare @RowCount int
  
  Select @rc=
  Select @RowCount=
  
  begin
  //create temp table save sp_who information
   create table #tspid(
   spid int null
   ecid int null
   status nchar() null
   loginname nchar() null
   hostname nchar() null
   blk bit null
   dbname nchar() null
   cmd nchar()
   )
  
  //create temp table save all SQL client IP and hostname and login time
  Create table #userip(
   [id]int identity()
   txt varchar()
  )
  
  //Create result table to return recordset
  Create table #result(
   [id]int identity()
   ClientIP varchar()
   hostname nchar()
   login_time datetime default(getdate())
  
   )
  //get host name by exec sp_who insert #tspid from sp_who
  insert into #tspid(spidecidstatusloginnamehostnameblkdbnamecmd) exec sp_who
  
  declare @cmdStr varchar()
   @hostName nchar()
   @userip varchar()
   @sendstr varchar()
  
  
  //declare a cursor from table #tspid
  declare tspid cursor
  for select distinct hostname from #tspid with (nolock) where spid>
  for read only
  
  open tspid
    fetch next from tspid into @hostname
    While @@FETCH_STATUS =
    begin
   select @cmdStr=ping +rtrim(@hostName)
  
   insert into #userip(txt) exec masterxp_cmdshell @cmdStr
  
   select @rowcount=count(id) from #userIP
  
  
   if @RowCount= //no IP feedback package
   begin
   insert into #Result(ClientIPhostname) values(Can not get feedback package from Ping!@hostname)
   end
   if @RowCount>
   begin
   select @userip=substring(txtcharindex([txt)+charindex(]txt)charindex([txt))
   from #userIP
   where txt like Pinging%
   
   insert into #Result(ClientIPhostname) values(@userIP@hostname)
   end
   select @rc=@@error
   if @rc=
   truncate table #userip //clear #userIP table
  
    fetch next from tspid into @hostname
   end
  
  close tspid
  deallocate tspid
  
  select * from #result with(nolock)
  
  drop table #tspid
  drop table #userip
  drop table #result
  end
  go
  exec usp_getClient_infor
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22055.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.