SQL Server Filter output of sp who2

Question

Under SQL Server  is there an easy way to filter the output of sp who2  Say I wanted to just show rows for a certain database  for example

User · Answer

Similar to KyleMit answer  its possible to select directly the tables used by SP WHO2  although I think it s only need dbo sysprocesses table   If someone open this SP  it can understand what it does  This is my best select to have a similar output as SP WHO2  select convert char 5  sp spid  as SPID            CASE lower sp status                   When  sleeping  Then lower sp status                   Else  upper sp status                END as Status           convert sysname  rtrim sp loginame   as LOGIN           CASE sp hostname                  When Null  Then                        When     Then                        Else    rtrim sp hostname                END as HostName           CASE isnull convert char 5  sp blocked   0                    When  0  Then                        Else isnull convert char 5  sp blocked   0                 END as BlkBy           case when sp dbid   0 then null when sp dbid  lt  gt  0 then db name sp dbid  end as DBName           sp cmd as Command           sp cpu as CPUTime           sp physical io as DiskIO           sp last batch as LastBatch           sp program name as ProgramName          from master dbo sysprocesses sp  nolock        Over this select  you can select the fields you need and have the order you want

User · Answer

I am writing here for future use of my own  It uses sp who2 and insert into table variable instead of temp table because Temp table cannot be used twice if you do not drop it  And shows blocked and blocker at the same line  --blocked  waiting becaused blocked by blocker --blocker  caused blocking declare  sp who2 table      SPID int      Status varchar max       Login varchar max       HostName varchar max       BlkBy varchar max       DBName varchar max       Command varchar max       CPUTime int      DiskIO int      LastBatch varchar max       ProgramName varchar max       SPID 2 int      REQUESTID int   insert into  sp who2 exec sp who2 select  w SPID blocked spid  w BlkBy blocker spid  tblocked text blocked text  tblocker text blocker text from     sp who2 w         inner join sys sysprocesses pblocked on w SPID   pblocked spid         cross apply sys dm exec sql text pblocked sql handle  tblocked         inner join sys sysprocesses pblocker on case when w BlkBy         then 0 else cast w BlkBy as int  end   pblocker spid         cross apply sys dm exec sql text pblocker sql handle  tblocker where   pblocked Status    SUSPENDED

User · Answer

You could save the results into a temp table  but it would be even better to go directly to the source on master dbo sysprocesses    Here s a query that will return almost the exact same result as sp who2   SELECT  spid          sp  status           loginame  Login           hostname           blocked BlkBy          sd name DBName           cmd Command          cpu CPUTime          physical io DiskIO          last batch LastBatch           program name  ProgramName    FROM master dbo sysprocesses sp  JOIN master dbo sysdatabases sd ON sp dbid   sd dbid ORDER BY spid    Now you can easily add any ORDER BY or WHERE clauses you like to get meaningful output     Alternatively  you might consider using Activity Monitor in SSMS  Ctrl   Alt   A  as well

User · Answer

A really easy way to do it is to create an ODBC link in EXCEL and run SP WHO2 from there   You can Refresh whenever you like and because it s EXCEL everything can be manipulated easily

User · Answer

based on http   web archive org web 20080218124946 http   sqlserver2005 databases aspfaq com how-do-i-mimic-sp-who2 html  i have created following script   which resolves finding active connections to any datbase using DMV this works under sql 2005   2008 and 2008R2  Following script uses sys dm exec sessions   sys dm exec requests   sys dm exec connections   sys dm tran locks  Declare  dbName varchar 1000  set  dbName  abc    WITH DBConn SPID  Status   Login  HostName DBName Command LastBatch ProgramName  As   SELECT      SPID   s session id      Status   UPPER COALESCE                       r status              ot task state              s status                      Login    s login name      HostName   COALESCE                       s  host name                                    DBName   COALESCE                       DB NAME COALESCE                               r database id                  t database id                                               Command   COALESCE                       r Command              r wait type              wt wait type              r last wait type                                LastBatch   COALESCE                       r start time              s last request start time                ProgramName   COALESCE                       s program name                            FROM     sys dm exec sessions s LEFT OUTER JOIN     sys dm exec requests r ON     s session id   r session id LEFT OUTER JOIN     sys dm exec connections c ON     s session id   c session id LEFT OUTER JOIN       SELECT          request session id          database id   MAX resource database id      FROM         sys dm tran locks     GROUP BY         request session id   t ON     s session id   t request session id LEFT OUTER JOIN     sys dm os waiting tasks wt ON      s session id   wt session id LEFT OUTER JOIN     sys dm os tasks ot ON      s session id   ot session id LEFT OUTER JOIN       SELECT         ot session id          CPU Time   MAX usermode time      FROM         sys dm os tasks ot     INNER JOIN         sys dm os workers ow     ON         ot worker address   ow worker address     INNER JOIN         sys dm os threads oth     ON         ow thread address   oth thread address     GROUP BY         ot session id   tt ON     s session id   tt session id WHERE     COALESCE               r command          r wait type          wt wait type          r last wait type           a         gt   COALESCE                             a           Select   from DBConn where DBName like      dbName

User · Answer

Yes  by capturing the output of sp who2 into a table and then selecting from the table  but that would be a bad way of doing it  First  because sp who2  despite its popularity  its an undocumented procedure and you shouldn t rely on undocumented procedures  Second because all sp who2 can do  and much more  can be obtained from sys dm exec requests and other DMVs  and show can be filtered  ordered  joined and all the other goodies that come with queriable rowsets

User · Answer

I made an improvement in order to obtain not only the blocked processes but also the blocking process  DECLARE  Table TABLE           SPID INT  Status VARCHAR MAX   LOGIN VARCHAR MAX   HostName VARCHAR MAX   BlkBy VARCHAR MAX   DBName VARCHAR MAX   Command VARCHAR MAX   CPUTime INT  DiskIO INT  LastBatch VARCHAR MAX   ProgramName VARCHAR MAX   SPID 1 INT  REQUESTID INT        INSERT INTO  Table EXEC sp who2  SELECT    FROM     Table WHERE     BlkBy not like           or     SPID in  SELECT  BlkBy from  Table where BlkBy not like         delete from  Table

User · Answer

Slight improvement to Astander s answer   I like to put my criteria at top  and make it easier to reuse day to day   DECLARE  Spid INT   Status VARCHAR MAX    Login VARCHAR MAX    HostName VARCHAR MAX    BlkBy VARCHAR MAX    DBName VARCHAR MAX    Command VARCHAR MAX    CPUTime INT   DiskIO INT   LastBatch VARCHAR MAX    ProgramName VARCHAR MAX    SPID 1 INT   REQUESTID INT      --SET  SPID   10     --SET  Status    BACKGROUND      --SET  LOGIN    sa      --SET  HostName    MSSQL-1      --SET  BlkBy   0     --SET  DBName    master      --SET  Command    SELECT INTO      --SET  CPUTime   1000     --SET  DiskIO   1000     --SET  LastBatch    10 24 10 00 00      --SET  ProgramName    Microsoft SQL Server Management Studio - Query      --SET  SPID 1   10     --SET  REQUESTID   0      SET NOCOUNT ON      DECLARE  Table TABLE              SPID INT              Status VARCHAR MAX               LOGIN VARCHAR MAX               HostName VARCHAR MAX               BlkBy VARCHAR MAX               DBName VARCHAR MAX               Command VARCHAR MAX               CPUTime INT              DiskIO INT              LastBatch VARCHAR MAX               ProgramName VARCHAR MAX               SPID 1 INT              REQUESTID INT           INSERT INTO  Table EXEC sp who2     SET NOCOUNT OFF     SELECT        FROM     Table     WHERE       Spid IS NULL OR SPID    Spid      AND   Status IS NULL OR Status    Status      AND   Login IS NULL OR Login    Login      AND   HostName IS NULL OR HostName    HostName      AND   BlkBy IS NULL OR BlkBy    BlkBy      AND   DBName IS NULL OR DBName    DBName      AND   Command IS NULL OR Command    Command      AND   CPUTime IS NULL OR CPUTime  gt    CPUTime      AND   DiskIO IS NULL OR DiskIO  gt    DiskIO      AND   LastBatch IS NULL OR LastBatch  gt    LastBatch      AND   ProgramName IS NULL OR ProgramName    ProgramName      AND   SPID 1 IS NULL OR SPID 1    SPID 1      AND   REQUESTID IS NULL OR REQUESTID    REQUESTID

User · Answer

You could try something like  DECLARE  Table TABLE          SPID INT          Status VARCHAR MAX           LOGIN VARCHAR MAX           HostName VARCHAR MAX           BlkBy VARCHAR MAX           DBName VARCHAR MAX           Command VARCHAR MAX           CPUTime INT          DiskIO INT          LastBatch VARCHAR MAX           ProgramName VARCHAR MAX           SPID 1 INT          REQUESTID INT    INSERT INTO  Table EXEC sp who2  SELECT    FROM     Table WHERE        And filter on what you require

User · Answer

This is the solution for you  http   blogs technet com b wardpond archive 2005 08 01 the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement aspx   select   from openrowset   SQLOLEDB    192 168 x x DATA    user    password    sp who

User · Answer

Extension of the first and best answer    I have created a stored procedure on the master database that you can then pass parameters to    such as the name of the database   USE master GO  CREATE PROCEDURE sp who db        sDBName varchar 200      null       sStatus varchar 200      null       sCommand varchar 200     null       nCPUTime int             null   AS DECLARE  Table TABLE       SPID INT      Status VARCHAR MAX       LOGIN VARCHAR MAX       HostName VARCHAR MAX       BlkBy VARCHAR MAX       DBName VARCHAR MAX       Command VARCHAR MAX       CPUTime INT      DiskIO INT      LastBatch VARCHAR MAX       ProgramName VARCHAR MAX       SPID 1 INT      REQUESTID INT    INSERT INTO  Table EXEC sp who2  SELECT        FROM     Table     WHERE     sDBName IS NULL OR DBName    sDBName      AND       sStatus IS NULL OR Status    sStatus      AND       sCommand IS NULL OR Command    sCommand      AND       nCPUTime IS NULL OR CPUTime  gt   nCPUTime  GO    I might extend it to add an order by parameter or even a kill paramatmer so it kills all connections to a particular data

User · Answer

One way is to create a temp table   CREATE TABLE  sp who2       SPID INT       Status VARCHAR 1000  NULL       Login SYSNAME NULL       HostName SYSNAME NULL       BlkBy SYSNAME NULL       DBName SYSNAME NULL       Command VARCHAR 1000  NULL       CPUTime INT NULL       DiskIO INT NULL       LastBatch VARCHAR 1000  NULL       ProgramName VARCHAR 1000  NULL       SPID2 INT    GO  INSERT INTO  sp who2 EXEC sp who2 GO  SELECT   FROM  sp who2 WHERE Login    bla  GO  DROP TABLE  sp who2 GO

User · Answer

There s quite a few good sp who3 user stored procedures out there - I m sure Adam Machanic did a really good one  AFAIK   Adam calls it Who Is Active  http   whoisactive com

[sql-server] SQL Server: Filter output of sp_who2

Examples related to sql-server

Examples related to sql-server-2016