declare @ip varchar(20) SELECT @ip = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID if @ip = '<local machine>' begin declare @tempTable table(ip varchar(100)) declare @sql varchar(100) set @sql='ping '+ HOST_NAME()+' -n 1' insert @tempTable exec master..xp_cmdshell @sqlselect top 1 @ip = replace(left(ip,charindex(':',ip)-1),'Reply from ','') from @tempTable where ip like 'reply from %:%' end -------测试信息输出--------------------------- print system_user+'---'+user+'---'+@ip
/*-- 所有连接本机的计算机名,IP地址,用户名,网卡物理地址 --*/create table #tb(id int identity(1,1),hostname nchar(128),loginname nchar(128),net_address nchar(12),net_ip nvarchar(15)) insert into #tb(hostname,net_address,loginname) select distinct hostname,net_address,loginame from master..sysprocesses where hostname<>''declare @id int,@sql varchar(500) select @id=max(id) from #tbcreate table #ip(id int,a varchar(8000)) while @id>0 begin select @sql='ping '+hostname from #tb where id=@id insert #ip(a) exec master..xp_cmdshell @sql update #ip set id=@id where id is null set @id=@id-1 endupdate #tb set net_ip=left(a,patindex('%:%',a)-1) from #tb a inner join ( select id,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip where a like 'Ping statistics for %:%') b on a.id=b.idselect * from #tbdrop table #tb,#ip --------------------------- SP_LOCK --检测SQL SERVER当前资源LOCK的状态。 SP_MONITOR --显示当前的SESSION的状态。 SP_WHO2 --显示当前SQL SERVER和client的连接状态和CPU,I/O的状态。. 列出特定用户的进程 此示例显示如何通过登录名查看有关单个当前用户的信息。USE master EXEC sp_who 'janetl'C. 显示所有活动进程 USE master EXEC sp_who 'active'D. 通过进程 ID 显示特定进程 USE master EXEC sp_who '10' --specifies the process_id ----------------------------------------------- create table #tspid( spid int null, ecid int null, status varchar(20) null, loginname varchar(20) null, hostname varchar(20) null, blk bit null, dbname varchar(20) null, cmd varchar(20) )insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who select * from #tspid where dbname='你的库'
用T-SQL获得当前连接客户端IP和机器名用T-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 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
那这个好使,我试过了Create proc usp_getClient_infor as set nocount onDeclare @rc int Declare @RowCount intSelect @rc=0 Select @RowCount=0begin --//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 onlyopen 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 tspidselect * from #result with(nolock)drop table #tspid drop table #userip drop table #result end go exec usp_getClient_infor
SELECT @ip = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID
if @ip = '<local machine>'
begin
declare @tempTable table(ip varchar(100))
declare @sql varchar(100)
set @sql='ping '+ HOST_NAME()+' -n 1'
insert @tempTable exec master..xp_cmdshell @sqlselect top 1 @ip = replace(left(ip,charindex(':',ip)-1),'Reply from ','')
from @tempTable where ip like 'reply from %:%'
end
-------测试信息输出---------------------------
print system_user+'---'+user+'---'+@ip
所有连接本机的计算机名,IP地址,用户名,网卡物理地址
--*/create table #tb(id int identity(1,1),hostname nchar(128),loginname nchar(128),net_address nchar(12),net_ip nvarchar(15))
insert into #tb(hostname,net_address,loginname)
select distinct hostname,net_address,loginame from master..sysprocesses where hostname<>''declare @id int,@sql varchar(500)
select @id=max(id) from #tbcreate table #ip(id int,a varchar(8000))
while @id>0
begin
select @sql='ping '+hostname from #tb where id=@id
insert #ip(a) exec master..xp_cmdshell @sql
update #ip set id=@id where id is null
set @id=@id-1
endupdate #tb set net_ip=left(a,patindex('%:%',a)-1)
from #tb a inner join (
select id,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip
where a like 'Ping statistics for %:%') b on a.id=b.idselect * from #tbdrop table #tb,#ip
---------------------------
SP_LOCK --检测SQL SERVER当前资源LOCK的状态。
SP_MONITOR --显示当前的SESSION的状态。
SP_WHO2 --显示当前SQL SERVER和client的连接状态和CPU,I/O的状态。. 列出特定用户的进程
此示例显示如何通过登录名查看有关单个当前用户的信息。USE master
EXEC sp_who 'janetl'C. 显示所有活动进程
USE master
EXEC sp_who 'active'D. 通过进程 ID 显示特定进程
USE master
EXEC sp_who '10' --specifies the process_id
-----------------------------------------------
create table #tspid(
spid int null,
ecid int null,
status varchar(20) null,
loginname varchar(20) null,
hostname varchar(20) null,
blk bit null,
dbname varchar(20) null,
cmd varchar(20)
)insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who select * from #tspid where dbname='你的库'
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
as
set nocount onDeclare @rc int
Declare @RowCount intSelect @rc=0
Select @RowCount=0begin
--//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 onlyopen 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 tspidselect * from #result with(nolock)drop table #tspid
drop table #userip
drop table #result
end
go
exec usp_getClient_infor
Up!
sql2005客户端IP是在哪个视图中看到的?