Modified SP_who

ALTER PROCEDURE sp_who2  --- 1995/11/03 10:16
    @loginame     sysname = NULL,
    @DBName nvarchar(25) = NULL
AS

SET NOCOUNT ON

DECLARE
	@retcode         int

DECLARE
	@sidlow         varbinary(85),
	@sidhigh        varbinary(85), 
	@sid1           varbinary(85), 
	@spidlow         int, 
	@spidhigh        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.

--------defaults
SELECT @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
SELECT @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

SELECT
	@spidlow         = 0,
	@spidhigh        = 32767

--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

--------

-- SELECT @sid1 = suser_sid(@loginame)
SELECT @sid1 = null
IF EXISTS(SELECT * from master.dbo.syslogins where loginname = @loginame)
	SELECT @sid1 = sid from master.dbo.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) IN ('active'))  --Special action, not sleeping.
   BEGIN
	   SELECT @loginame = lower(@loginame)
	   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
	spid,
	status,
	sid,
	hostname,
	program_name,
	cmd,
	cpu,
	physical_io,
	blocked,
	dbid,
	convert(sysname, rtrim(loginame))
	        as loginname,
	spid as 'spid_sort',
	substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
	  + substring( convert(varchar,last_batch,113) ,13 ,8 )
	       as 'last_batch_char'
      INTO    #tb1_sysprocesses
      FROM master.dbo.sysprocesses   (nolock)



--------Screen out any rows?

IF (@loginame IN ('active'))
   DELETE #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    IN (
                     'AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'
                                  )

         and     blocked       = 0



--------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
      where
--             sid >= @sidlow
--      and    sid <= @sidhigh
--      and
             spid >= @spidlow
      and    spid <= @spidhigh




--Add EventInfo Column to #tb1_sysprocesses
ALTER TABLE #tb1_sysprocesses
	ADD EventInfo text

--CREATE TEMP TABLE TO INPUT DBCC INFO
CREATE TABLE #tmpDBCCInfo
	(
		EventType varchar(8000),	
		Parameters int,
		EventInfo varchar(8000)
	)
	

DECLARE @DBCCExecute varchar(100)
DECLARE @SPID int
DECLARE dbcc_cursor CURSOR FOR 
	SELECT SPID 
	FROM #tb1_sysprocesses

OPEN dbcc_cursor 
FETCH NEXT FROM dbcc_cursor INTO @SPID
WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @DBCCExecute  = 'DBCC inputbuffer(' + CAST(@SPID as varchar) + ') WITH TABLERESULTS, NO_INFOMSGS'
		INSERT INTO #tmpDBCCInfo 
			EXECUTE(@DBCCExecute)
		UPDATE #tb1_sysprocesses 
			SET EventInfo = (SELECT CAST(EventInfo as varchar(8000)) FROM #tmpDBCCInfo)
		WHERE
			SPID = @SPID

		DELETE FROM #tmpDBCCInfo
		FETCH NEXT FROM dbcc_cursor INTO @SPID
	END	

DEALLOCATE dbcc_cursor


--------Output the report.

DECLARE @SQL varchar(8000)

SET @SQL = 
'SET NOCOUNT ON

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 + ')
            ,EventInfo
      FROM
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      WHERE
             spid >= ' + @charspidlow  + ' and spid <= ' + @charspidhigh + ' '

IF @Loginame IS NOT NULL 
	BEGIN
		SET @SQL = @SQL  + ' AND LoginName = ''' + @loginame + ''''
	END
IF @DBName IS NOT NULL 
	BEGIN
		SET @SQL = @SQL  + ' AND dbid =  db_id(''' + @DBName + ''')'

	END
SET @SQL = @SQL +
'      -- (Seems always auto sorted.)   order by spid_sort


'
PRINT @SQL
EXECUTE (@SQL)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
             sid >= ' + @charsidlow  + '
      and    sid <= ' + @charsidhigh + '
      and
**************/

SET NOCOUNT OFF





LABEL_86RETURN:

IF (object_id('tempdb..#tb1_sysprocesses') is not null)
            DROP TABLE #tb1_sysprocesses
IF (object_id('tempdb..#tmpDBCCInfo') is not null)
            DROP TABLE #tmpDBCCInfo



RETURN @retcode -- sp_who2




GO