SP_WHO2 With a Order by Clause & Monitor Server

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

SP_WHO 2 ORDER BY

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

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

      Login  VARCHAR(255),HostName  VARCHAR(255),

      BlkBy  VARCHAR(255),DBName  VARCHAR(255),

      Command VARCHAR(255),CPUTime INT,

      DiskIO INT,LastBatch VARCHAR(255),

      ProgramName VARCHAR(255),SPID2 INT,

      REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

  SELECT *

    FROM #sp_who2

   WHERE DBName <> 'master' -- Add any filtering of the results here

ORDER BY status ASC -- Add any sorting of the results here

DROP TABLE #sp_who2

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

Running Queries

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

SELECT p.spid

     , p.status

     , p.hostname

     , p.loginame

     , p.cpu

     , r.start_time

     , r.command

     , p.program_name

     , text

  

       FROM sys.dm_exec_requests AS r,

            master.dbo.sysprocesses AS p

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

      WHERE p.status NOT IN ('sleeping', 'background')

        AND r.session_id = p.spid

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

All Queries

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

     SELECT sqltext.TEXT

          , req.session_id

          , req.status

          , req.command

          , req.cpu_time

          , req.total_elapsed_time

       FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext