显示当前连接的服务器名,怎么以编程或Sql语句的方式获取本机当前连接的 SQL server 的服务名称,看认真了,是服务,不是服务器。...

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='DB_WIP',@includeip=1

取消

评论