Q9.2: sp_whodo
Sybase System 10.x
<![CDATA[
use master
go
drop procedure sp_whodo
go
create procedure sp_whodo @loginame varchar(30) = NULL as
declare @low int
declare @high int
declare @spidlow int
declare @spidhigh int
select @low = 0, @high = 32767, @spidlow = 0, @spidhigh = 32767
if @loginame is not NULL
begin
select @low = suser_id(@loginame), @high = suser_id(@loginame)
if @low is NULL
begin
if @loginame like "[0-9]%"
begin
select @spidlow = convert(int, @loginame),
@spidhigh = convert(int, @loginame),
@low = 0, @high = 32767
end
else
begin
print "No login exists with the supplied name."
return (1)
end
end
end
select
spid,
status,
substring(suser_name(suid),1,12) loginame,
hostname,
convert(char(3),blocked) blk,
convert(char(7),isnull(time_blocked, 0)) blk_sec,
convert(char(16),program_name) program,
convert(char(7),db_name(dbid)) dbname,
convert(char(16),cmd) cmd,
convert(char(6),cpu) cpu,
convert(char(7),physical_io) io,
convert(char(16),isnull(tran_name, "")) tran_name
from master..sysprocesses
where suid >= @low and suid <= @high
and spid >= @spidlow and spid <= @spidhigh
return (0)
go
grant execute on sp_whodo to public
go
]]>
Sybase 4.x
<![CDATA[
use master
go
drop procedure sp_whodo
go
create procedure sp_whodo @loginame varchar(30) = NULL as
declare @low int
declare @high int
declare @spidlow int
declare @spidhigh int
select @low = 0, @high = 32767, @spidlow = 0, @spidhigh = 32767
if @loginame is not NULL
begin
select @low = suser_id(@loginame), @high = suser_id(@loginame)
if @low is NULL
begin
if @loginame like "[0-9]%"
begin
select @spidlow = convert(int, @loginame),
@spidhigh = convert(int, @loginame),
@low = 0, @high = 32767
end
else
begin
print "No login exists with the supplied name."
return (1)
end
end
end
select
spid,
status,
substring(suser_name(suid),1,12) loginame,
hostname,
convert(char(3),blocked) blk,
convert(char(16),program_name) program,
convert(char(7),db_name(dbid)) dbname,
convert(char(16),cmd) cmd,
convert(char(6),cpu) cpu,
convert(char(7),physical_io) io
from master..sysprocesses
where suid >= @low and suid <= @high
and spid >= @spidlow and spid <= @spidhigh
return (0)
go
grant execute on sp_whodo to public
go
]]>