SP_WHO3

Wouldn't it be nice to have the ability to be able to pass more parameters to SP_WHO or SP_WHO2? Well I just wrote SP_WHO3 which allows you to pass the following parameters: @DBName, @SPID, @Status, @Login, @hostName, @Command, @CPUtime_floor, @DiskIO_floor, @ProgramName and @precision_bit.

Furthermore, you can set @precision_bit=0 to get a close match (see comments in the code). Enjoy!

SP_WHO3:

CREATE PROC sp_who3

(      @exact_flag          bit=1,

       @DBName              varchar(100)='*All*',

       @SPID                int=-1,

       @Status              varchar(40)='*All*',

       @Login               varchar(50)='*All*',

       @hostName            varchar(100)='*All*',

       @Command             varchar(200)='*All*',

       @CPUtime_floor       bigint=-1,

       @DiskIO_floor        bigint=-1,

       @ProgramName         varchar(100)='*All*'

)

AS

/******************************************************************

 Author:      Alan Burstein

 Created on:  9/5/2012

 Updated on:  4/19/2013

 

 Use:

 Returns the same columns as SP_WHO2 but you can pass parameters

 and filter the result set.

 

 With @exact_flag=1 (default) you only get records where there

 is an EXACT (=) match. For example, when @exact_flag=1 and

 @Command='TASK' nothing will be returned. If exact_match_flag=0

 and @Command='TASK' then records where Command="TASK MANAGER",

 Command="BRKR TASK", etc will be returned.

******************************************************************/

BEGIN

 SET NOCOUNT ON;

 

 -- Create table to hold the results of EXEC SP_WHO2

 DECLARE @sp_who3 TABLE

 (     SPID          int,

       [Status]      varchar(40),

       [Login]       varchar(50),

       HostName      varchar(100),

       BlkBy         varchar(4),

       DBName        varchar(100),

       Command       varchar(200),

       CPUtime       bigint,

       DiskIO        bigint,

       LastBatch     varchar(100),

       ProgramName   varchar(100),

       SPID2         int,

       REQUESTID     int);

 

 INSERT INTO @sp_who3 EXEC SP_WHO2;

 

 -- output

 IF @exact_flag=1

 BEGIN

  SELECT * FROM @sp_who3

  WHERE (DBName=@DBName OR @DBName='*All*') AND

    ([Status]=@Status OR @Status='*All*') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login]=@Login OR @Login='*All*') AND

    (HostName=@hostName OR @hostName='*All*') AND

    (Command=@Command OR @Command='*All*') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName=@ProgramName OR @ProgramName='*All*')

  ORDER BY LastBatch DESC

 END

 ELSE

 BEGIN

  SELECT @DBName='%'+CAST(@DBName as varchar(100))+'%',

    @Status='%'+CAST(@Status as varchar(40))+'%',

    @Login='%'+CAST(@Login AS varchar(50))+'%',

    @hostName='%'+CAST(@hostName AS varchar(100))+'%',

    @Command='%'+CAST(@Command AS varchar(200))+'%',

    @ProgramName='%'+CAST(@ProgramName AS varchar(100))+'%'

 

  SELECT * FROM @sp_who3

  WHERE (DBName LIKE @DBName OR @DBName='%*All*%') AND

    ([Status] LIKE @Status OR @Status='%*All*%') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login] LIKE @Login OR @Login='%*All*%') AND

    (HostName LIKE @hostName OR @hostName='%*All*%') AND

    (Command=@Command OR @Command='%*All*%') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName LIKE @ProgramName OR @ProgramName='%*All*%')

  ORDER BY LastBatch DESC

 END

END

GO

--SP_WHO3 @exact_flag=0, @ProgramName='repl', @CPUtime_floor=10000

 

Example:

Epilogue

Before posting this I googled SP_WHO3. It turns out I was not the first to come up with this idea. Bummer. My SP_WHO3 is still great.

--ab

Last Updated: 4/19/2013 (code cleanup)