Wouldn't it be usefull if sp_who showed the query the user is running.

I can't remember the number of times I have run sp_who2 and then dbcc inputbuffer (##) to quickly check out blocking problems. Wouldn't it be usefull if this information was included in sp_who. Well, with SQL Server 2005 we can finally do this thanks to the power of the function sys.dm_exec_sql_text(). All I really had to change to sp_who2 was adding "OUTER apply sys.dm_exec_sql_text(s.sql_handle)" and then use the stmt_start and stmt_end fields to extract the substring from the query.

Install the procedure below and run:

exec sp_whoo

Executing the procedure will give you the following results:

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID	REQUESTID	SqlStatement
52	sleeping	sa	MyLaptop	.	test_db	AWAITING COMMAND	110	43	05/24 10:54:40	Microsoft SQL Server Management Studio - Query	52	0	select * from sys.tables

Changes versus the standard sp_who2 in SQL Server 2005:

• Removed all spids before 50 (system threads)
• Removed your own spid.

Limitations:

• Queries running through the service broker do not always return a sql handle. So no data can be shown.
• When accessing encrypted objects, no information can be shown either

You can download the script sp_whoo.sql or read the code below:

USE master
GO
IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_whoo' AND OBJECTPROPERTY(id, 'IsProcedure') = 1))
      DROP PROCEDURE dbo.sp_whoo
GO
CREATE PROCEDURE dbo.sp_whoo(@loginame sysname = NULL)
AS
 
SET NOCOUNT ON
 
declare @retcode         int
 
declare @sidlow         varbinary(85)
       ,@sidhigh        varbinary(85)
       ,@sid1           varbinary(85)
       ,@spidlow         int
       ,@spidhigh        int
       ,@spidself        int
 
declare @charMaxLenLoginName      varchar(6)
       ,@charMaxLenDBName         varchar(6)
       ,@charMaxLenCPUTime        varchar(10)
       ,@charMaxLenDiskIO         varchar(10)
       ,@charMaxLenHostName       varchar(10)
       ,@charMaxLenProgramName    varchar(10)
       ,@charMaxLenLastBatch      varchar(10)
       ,@charMaxLenCommand        varchar(10)
 
declare @charsidlow              varchar(85)
       ,@charsidhigh             varchar(85)
       ,@charspidlow              varchar(11)
       ,@charspidhigh             varchar(11)
 
SELECT @retcode = 0      -- 0=good ,1=bad.
      ,@sidlow  = convert(varbinary(85), (replicate(char(0), 85)))
      ,@sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
 
SELECT @spidlow         = 50
      ,@spidhigh        = 32767
      ,@spidself        = @@spid
 
--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED
 
-- select @sid1 = suser_sid(@loginame)
SELECT @sid1 = NULL
IF EXISTS(SELECT * FROM sys.syslogins WHERE loginname = @loginame)
    SELECT @sid1 = sid FROM sys.syslogins WHERE loginname = @loginame
 
IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   SELECT @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active'))  --Special action, not sleeping.
   begin
   SELECT @loginame = lower(@loginame collate Latin1_General_CI_AS)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   SELECT
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
raiserror(15007,-1,-1,@loginame)
SELECT @retcode = 1
GOTO LABEL_86RETURN
 
 
LABEL_17PARM1EDITED:
 
 
--------------------  Capture consistent sysprocesses.  -------------------
 
SELECT s.spid , s.status ,s.sid ,s.hostname ,s.program_name ,s.cmd ,s.cpu ,s.physical_io ,s.blocked ,s.dbid
 , convert(sysname, rtrim(s.loginame)) AS loginname
 , s.spid AS 'spid_sort'
 , substring( convert(varchar, s.last_batch, 111) ,6  ,5 ) + ' ' + substring(convert(varchar, s.last_batch, 113), 13, 8 ) AS 'last_batch_char'
 , s.request_id
 , case when s.stmt_start=0 AND s.stmt_end=0 then qt.text
        else substring(qt.text, (s.stmt_start/2)+1,
                (case when s.stmt_end = -1
                      then len(convert(nvarchar(max), qt.text))*2
                      else s.stmt_end 
                 end - s.stmt_start) / 2) 
   end AS 'sql_statement'
INTO #tb1_sysprocesses
FROM master.dbo.sysprocesses s WITH (nolock)
  OUTER apply sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE ((s.spid > @spidlow AND s.spid < @spidhigh) OR (s.spid = @spidlow AND s.spid = @spidhigh))
  AND s.spid <> @spidself
  AND (s.sid = @sidlow OR @sidlow <> @sidhigh)
 
IF @@error <> 0
    begin
        SELECT @retcode = @@error
        GOTO LABEL_86RETURN
    end
 
--------Screen out any rows?
 
IF (@loginame IN ('active'))
  BEGIN
    DELETE #tb1_sysprocesses
    WHERE lower(status) = 'sleeping'
      AND upper(cmd)    IN ('AWAITING COMMAND','LAZY WRITER','CHECKPOINT SLEEP')
      AND blocked       = 0
  END
 
 
--------Prepare to dynamically optimize column widths.
SELECT
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow    = convert(varchar, @spidlow)
   ,@charspidhigh   = convert(varchar, @spidhigh)
 
SELECT
     @charMaxLenLoginName   = convert( varchar, isnull( max( datalength(loginname)) ,5))
    ,@charMaxLenDBName      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
    ,@charMaxLenCPUTime     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
    ,@charMaxLenDiskIO      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
    ,@charMaxLenCommand     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
    ,@charMaxLenHostName    = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8))
    ,@charMaxLenProgramName = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
    ,@charMaxLenLastBatch   = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
FROM #tb1_sysprocesses
 
--------Output the report.
 
EXEC('
SET nocount off
SELECT
             SPID          = convert(char(5),spid)
            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END
            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END
 
            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END
            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
            ,REQUESTID     = convert(char(5),request_id)
            ,SqlStatement  = rtrim(ltrim(replace(sql_statement, char(13)+char(10), '' '')))
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      -- (Seems always auto sorted.)   order by spid_sort
')
 
LABEL_86RETURN:
IF (object_id('tempdb..#tb1_sysprocesses') IS NOT NULL)
            DROP TABLE #tb1_sysprocesses
 
RETURN @retcode -- sp_whoo
GO