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
