CREATE PROC sp_who3
AS
    BEGIN
      SET NOCOUNT ON

      -- NULL, CMD, CPU, DISKIO, LASTBATCH, DBNAME
      --DECLARE @SORTBY       VARCHAR(30)
      --SET @SORTBY           = NULL --'CPU'

      DECLARE @sp_who3 table(
            spid        INT         NOT NULL,
            status            VARCHAR(255)      NOT NULL,
            loginname   VARCHAR(255)      NOT NULL,
            hostname    VARCHAR(255)      NULL,
            blocked           INT         NOT NULL,
            dbname            VARCHAR(255)      NULL,
            cmd         VARCHAR(255)      NOT NULL,
            cpu         BIGINT            NOT NULL,
            diskio            BIGINT            NOT NULL,
            memusage    BIGINT            NOT NULL,
            opentrans   INT         NOT NULL,
            lastbatch   DATETIME    NOT NULL,
            logintime   DATETIME    NOT NULL,
            programname VARCHAR(255)      NULL,
            eventinfo   VARCHAR(255)      NULL
      )


      CREATE TABLE #inputbuffer (
            EventType   varchar(255),
            Parameters  int,
            EventInfo   varchar(255)
      )



      INSERT INTO @sp_who3 (  spid,
                        status,
                        loginname,
                        hostname,
                        blocked,
                        dbname,
                        cmd,
                        cpu,
                        diskio,
                        memusage,
                        opentrans,
                        lastbatch,
                        logintime,
                        programname)
      SELECT      sp.spid,
            sp.status,
            sp.loginame,
            nullif(rtrim(ltrim(sp.hostname)), ''),
            sp.blocked,
            sd.name,
            sp.cmd,
            sp.cpu,
            sp.physical_io,
            sp.memusage,
            sp.open_tran,
            sp.last_batch,
            sp.login_time,
            nullif(rtrim(ltrim(sp.program_name)), '')
      from master..sysprocesses sp
            LEFT OUTER JOIN master..sysdatabases sd ON sd.dbid = sp.dbid

      declare     @spid       varchar(30),
            @fetch_status     int

      DECLARE dbcc_inputbuffer CURSOR FOR
      SELECT CONVERT(VARCHAR(30), spid)
      FROM @sp_who3

      OPEN dbcc_inputbuffer

      FETCH NEXT FROM dbcc_inputbuffer
      INTO @spid

      set @fetch_status = @@fetch_status

      WHILE @fetch_status = 0
      BEGIN

            INSERT INTO #inputbuffer
            exec('dbcc inputbuffer(' + @spid + ')')

            UPDATE @sp_who3
            SET eventinfo = (SELECT EventInfo FROM #inputbuffer)
            WHERE spid = @spid

            TRUNCATE TABLE #inputbuffer

            FETCH NEXT FROM dbcc_inputbuffer
            INTO @spid

            set @fetch_status = @@fetch_status
      END
      DROP TABLE #inputbuffer

            insert into tSPWho3
            SELECT * FROM @sp_who3
            ORDER BY logintime

      RETURN 0
END
GO