use sybsystemprocs
go

if object_id('sp_whodo') is not null
begin
    drop procedure sp_whodo
    if object_id('sp_whodo') is not null
        print '<<< Failed to drop procedure sp_whodo >>>'
    else
        print '<<< Dropped procedure sp_whodo >>>'
end
go

create procedure sp_whodo @loginame varchar(30) = NULL
as

  declare @low     int
         ,@high    int
         ,@spidlow int
         ,@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 "Login %1! does not exist.", @loginame
               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

if object_id('sp_whodo') is not null
begin
    print '<<< Created procedure sp_whodo >>>'
    grant execute on sp_whodo to public
end
else
    print '<<< Failed to create procedure sp_whodo >>>'
go

