SQL Server Performance

sp_who2_ext

Discussion in 'Contribute Your SQL Server Scripts' started by chopeen, Jan 26, 2005.

  1. chopeen Member

    /*****************************************************************
    * A modified version of sp_who2 stored procedure - it allows to *
    * filter logins. *
    * *
    * *
    * ver 1.0 (2005.01.26) *
    *****************************************************************/

    -- DROP PROCEDURE dbo.sp_who2_ext
    -- GO

    -- the stored procedure
    CREATE PROCEDURE dbo.sp_who2_ext
    @Login varchar(100) = '%',
    @exact bit = 0
    AS
    CREATE TABLE #table
    (
    SPID int,
    Status varchar(100),
    Login varchar(100),
    HostName varchar(100),
    BlkBy varchar(100),
    DBName varchar(100),
    Command varchar(100),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(100),
    ProgramName varchar(100),
    SPID1 int
    )

    INSERT INTO #table
    EXEC dbo.sp_who2

    DECLARE @Login_where varchar(102)
    IF @exact = 1
    SET @Login_where = @Login
    ELSE
    SET @Login_where = '%' + @Login + '%'

    SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID1
    FROM #table
    WHERE Login LIKE @Login_where

    DROP TABLE #table
    GO

    -- example usage
    EXEC dbo.sp_who2_ext -- this works like a regular sp_who2
    EXEC dbo.sp_who2_ext 'marekg' -- this shows logins that contain 'marekg'
    EXEC dbo.sp_who2_ext 'marekg', 0 -- ditto
    EXEC dbo.sp_who2_ext 'marekg', 1 -- this shows logins that equal 'marekg'
    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  2. Madhivanan Moderator

Share This Page