Getting Query text for running process

 

DECLARE @SPID int

 

CREATE TABLE #process (

  SPID int NOT NULL

, Status varchar (255) NOT NULL

, Login varchar (255) NOT NULL

, HostName varchar (255) NOT NULL

, BlkBy varchar(10) NOT NULL

, DBName varchar (255) null

, Command varchar (255) NOT NULL

, CPUTime int NOT NULL

, DiskIO int NOT NULL

, LastBatch varchar (255) NOT NULL

, ProgramName varchar (255)

, SPID2 int NOT NULL

, REQUESTID int NOT NULL

)

 

CREATE TABLE #inputbuffer(

      SPID int NOT NULL,

      EventType varchar(255),

      Paramters int NOT NULL,

      EventInfo varchar(8000))

 

INSERT #Process

EXEC sp_who2

 

DECLARE Proce CURSOR FAST_FORWARD FOR

SELECT SPID FROM #Process

 

OPEN Proce

 

FETCH NEXT FROM buf

INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @proccid VARCHAR(10)

SET @proccid = CAST(@SPID AS varchar(10))

DECLARE @tempInput TABLE (        

EventType varchar(255),        

Paramters int,        

EventInfo varchar(8000)

    )

 

    INSERT @tempInput

    EXEC ('dbcc inputbuffer(' + @proccid + ') WITH NO_INFOMSGS')

 

    INSERT #inputbuffer

    SELECT @SPID, * FROM @tempInput

 

    DELETE FROM @tempInput

 

    FETCH NEXT FROM Proce

    INTO @SPID

 

END

 

CLOSE Proce

DEALLOCATE Proce

 

SET NOCOUNT OFF

 

SELECT

    s.SPID,

    d.EventInfo,

    s.Status,

    s.Login,

    s.HostName,

    s.BlkBy,

    s.DBName,

    s.Command,

    s.CPUTime,

    s.DiskIO,

    s.LastBatch,

    s.ProgramName,

    s.REQUESTID

FROM

    #process s

    LEFT JOIN #inputbuffer d ON

    s.SPID = d.SPID