use model go create proc send_message as begin create table #tspid( spid int null, ecid int null, status varchar(200) null, loginname varchar(200) null, hostname varchar(200) null, blk bit null, dbname varchar(200) null, cmd varchar(200) )insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who create table #userip(id int identity,hostname varchar(200),txt varchar(8000))declare @cmdStr varchar(100), @hostName varchar(30), @userip varchar(20), @sendstr varchar(100)declare tspid cursor for select hostname from #tspid where spid>50 open tspid fetch next from tspid into @hostname WHILE @@FETCH_STATUS = 0 begin create table #table (id int identity,txt varchar(8000)) select @cmdStr='ping '+@hostName insert #table(txt) exec master..xp_cmdshell @cmdStr insert #userip select @hostname,substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2 fetch next from tspid into @hostname end select * from #userip drop table #tspid ,#userip endgo--调用: exec send_message
declare @cmdStr varchar(100) create table #table(id int identity,txt varchar(1000)) create table #t(hostname varchar(100), ip varchar(100)) declare @id int declare @hostname varchar(100) set @id = 50 set nocount on while exists (select top 1 HostName from master..sysprocesses where spid > @id) begin select top 1 @cmdStr='ping '+HostName, @id = spid, @hostname = hostname from master..sysprocesses where spid > @id order by spid insert into #table(txt) exec master..xp_cmdshell @cmdStr insert #T select @hostname, substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2 truncate table #table end set nocount off select distinct * from #t drop table #table drop table #t
go
create proc send_message as begin
create table #tspid(
spid int null,
ecid int null,
status varchar(200) null,
loginname varchar(200) null,
hostname varchar(200) null,
blk bit null,
dbname varchar(200) null,
cmd varchar(200)
)insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who create table #userip(id int identity,hostname varchar(200),txt varchar(8000))declare @cmdStr varchar(100), @hostName varchar(30), @userip varchar(20), @sendstr varchar(100)declare tspid cursor for select hostname from #tspid where spid>50
open tspid fetch next from tspid into @hostname
WHILE @@FETCH_STATUS = 0
begin
create table #table (id int identity,txt varchar(8000))
select @cmdStr='ping '+@hostName
insert #table(txt) exec master..xp_cmdshell @cmdStr
insert #userip select @hostname,substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2
fetch next from tspid into @hostname
end select * from #userip
drop table #tspid ,#userip
endgo--调用:
exec send_message
create table #table(id int identity,txt varchar(1000))
create table #t(hostname varchar(100), ip varchar(100))
declare @id int
declare @hostname varchar(100)
set @id = 50
set nocount on
while exists (select top 1 HostName from master..sysprocesses where spid > @id)
begin
select top 1 @cmdStr='ping '+HostName, @id = spid, @hostname = hostname from master..sysprocesses where spid > @id order by spid
insert into #table(txt) exec master..xp_cmdshell @cmdStr
insert #T select @hostname, substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2
truncate table #table
end
set nocount off
select distinct * from #t
drop table #table
drop table #t
然后察看一下系统表sysprocesses就应该能找出点规律来
sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库 字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库 当前配置选项
sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexes 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
sysusers 每个数据库 用户
具体见联机帮助。