SQL Server获取连接的IP地址

来源：http://www.itpub.net/thread-193247-1-1.html

先保存，以后研究一下

*--获取连接SQL服务器的信息  

           所有连接本机的:操作的数据库名,计算机名,用户名,网卡物理地址,IP地址,程序名  
-*/  

/*--调用示例  
           --显示所有本机的连接信息  
           exec  p_getlinkinfo  

           --显示所有本机的连接信息,包含ip地址  
           exec  p_getlinkinfo  @includeip=1  

           --显示连接指定数据库的信息  
           exec  p_getlinkinfo  '客户资料'  
--*/  
if  exists  (select  *  from  dbo.sysobjects  where  id  =  object_id(N'[dbo].[p_getlinkinfo]')  and  OBJECTPROPERTY(id,  N'IsProcedure')  =  1)  
drop  procedure  [dbo].[p_getlinkinfo]  
GO  

create  proc  p_getlinkinfo  
@dbname  sysname=null,                        --要查询的数据库名,默认查询所有数据库的连接信息  
@includeip  bit=0                                    --是否显示IP地址,因为查询IP地址比较费时,所以增加此控制  
as  
declare  @dbid  int  
set  @dbid=db_id(@dbname)  

create  table  #tb(id  int  identity(1,1),dbname  sysname,hostname  nchar(128),loginname  nchar(128),net_address  nchar(12),net_ip  nvarchar(15),prog_name  nchar(128))  
insert  into  #tb(hostname,dbname,net_address,loginname,prog_name)  
select  distinct  hostname,db_name(dbid),net_address,loginame,program_name  from  master..sysprocesses  
           where  hostname<>''  and  (@dbid  is  null  or  dbid=@dbid)  

if  @includeip=0  goto  lb_show    --如果不显示IP地址,就直接显示  

declare  @sql  varchar(500),@hostname  nchar(128),@id  int  
create  table  #ip(hostname  nchar(128),a  varchar(200))  
declare  tb  cursor  local  for  select  distinct  hostname  from  #tb  
open  tb  
fetch  next  from  tb  into  @hostname  
while  @@fetch_status=0  
begin  
           set  @sql='ping  '+@hostname+'  -a  -n  1  -l  1'  
           insert  #ip(a)  exec  master..xp_cmdshell  @sql  
           update  #ip  set  hostname=@hostname  where  hostname  is  null  
           fetch  next  from  tb  into  @hostname  
end  

update  #tb  set  net_ip=left(a,patindex('%:%',a)-1)  
from  #tb  a  inner  join  (  
select  hostname,a=substring(a,patindex('Ping  statistics  for  %:%',a)+20,20)  from  #ip  
           where  a  like  'Ping  statistics  for  %:%')  b  on  a.hostname=b.hostname  

lb_show:  
select  id,数据库名=dbname,客户机名=hostname,用户名=loginname  
           ,网卡物理地址=net_address,IP地址=net_ip,应用程序名称=prog_name  from  #tb  

go