SQL获得当前连接客户端IP和机器名

create proc   usp_getClient_infor     
  as   
  set   nocount   on   
  Declare   @rc   int   
  Declare   @RowCount   int   
  Select   @rc=0   
  Select   @RowCount=0   
  begin   
  --//create   temp   table   ,save   sp_who   information   
  create   table   #tspid(   
  spid   int   null,   
  ecid   int   null,   
  status   nchar(60)   null,   
  loginname   nchar(256)   null,   
  hostname   nchar(256)   null,   
  blk   bit   null,   
  dbname   nchar(256)   null,   
  cmd   nchar(32)   
  )   
  --//create   temp   table   save   all   SQL   client   IP   and   hostname   and   login   time   
  Create   table   #userip(   
  [id]int   identity(1,1),   
  txt   varchar(1000),   
  )   
  --//Create   result   table   to   return   recordset   
  Create   table   #result(   
  [id]int   identity(1,1),   
  ClientIP   varchar(1000),   
  hostname   nchar(256),   
  login_time   datetime   default(getdate())   
  )   
  --//get   host   name   by   exec   sp_who   ,insert   #tspid   from   sp_who,   
  insert   into   #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd)   exec   sp_who     
  declare   @cmdStr   varchar(100),     
  @hostName   nchar(256),     
  @userip   varchar(20),     
  @sendstr   varchar(100)   
  --//declare   a   cursor   from   table   #tspid   
  declare   tspid   cursor   for   select   distinct   hostname   from   #tspid   with   (nolock)   where   spid>50   for   read   only   
  open   tspid   
  fetch   next   from   tspid   into   @hostname   
  While   @@FETCH_STATUS   =   0   
  begin   
  select   @cmdStr='ping   '+rtrim(@hostName)   
  insert   into   #userip(txt)   exec   master..xp_cmdshell   @cmdStr     
  select   @rowcount=count(id)   from   #userIP   
  if   @RowCount=2   --//no   IP   feedback   package   
  begin   
  insert   into   #Result(ClientIP,hostname)   values('Can   not   get   feedback   package   from   Ping!',@hostname)   
  end   
  if   @RowCount>2     
  begin   
  select   @userip=substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1)     
  from   #userIP   
  where   txt   like   'Pinging%'   
  insert   into   #Result(ClientIP,hostname)   values(@userIP,@hostname)   
  end   
  select   @rc=@@error   
  if   @rc=0   
  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