if object_id('p_getlinkinfo','P')is not null drop proc p_getlinkinfo

go

create proc p_getlinkinfo

@dbname sysname=null, --要查詢的數據庫名,默認表示所有

@includeip bit=0 --是否顯示IP信息

as

begin

declare @dbid int

set @dbid=db_id(@dbname)

if object_id('tempdb..#tb')is not null drop table #tb

if object_id('tempdb..#ip')is not null drop table #ip

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,

net_ip,

prog_name

from #tb

end

go

exec p_getlinkinfo @dbname='master',@includeip=1

SELECT * FROM sys.configurations

ORDER BY name ;

sp_configure 'show advanced options', 1;

go

reconfigure;

go

sp_configure 'xp_cmdshell', 1;

go

reconfigure;

go