SQL Server Central: sp_what

sp_what - SQL Server Central
This is a replacement for SP_who and modification of sp_who2.

It
will always list only active, nonsystem processes, and will list the
number of seconds a transaction has been running for. Some transactions
do not report a last batch time, so I forced a large value to display
so you can see any issue with that transaction. Its sorted by spid, so
you can se more easily whats blocking, and the columns are arranged to
fit all info better on the screen (1280x1024).

CREATE PROCEDURE sp_what  --- 2006/3/22
--    @loginame     sysname = NULL
as

set nocount on

declare
@retcode         int, @loginame  sysname
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)
--------
select  @retcode         = 0      -- 0=good ,1=bad.
--------------------------------------------------------------

if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

--------------------  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',last_batch
INTO    #tb1_sysprocesses
from master.dbo.sysprocesses   (nolock)

--------Screen out any rows
DELETE #tb1_sysprocesses
where   lower(status)  = 'sleeping'
and     upper(cmd)    IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER'
)

and     blocked       = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE()) 
where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch < '01/01/1950'
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN program_name varchar(20)
--------Prepare to dynamically optimize column widths.
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
spid >= 0 and spid <= 32767



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


EXECUTE(
'SET nocount off
SELECT   SPID          = convert(char(5),spid)
,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
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
,Status        =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else                   upper(status)
END
,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
,LBDate = substring(last_batch_char,1,5)
,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
from
#tb1_sysprocesses  --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
-- (Seems always auto sorted.)   order by SPID
SET nocount on')

drop table #tb1_sysprocesses
--return @retcode 
GO