Estensione della sp_who

Per lanciarla:

Exec sp_processi 'active'

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_processi] Script Date: 24/04/2015 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_processi] –'active' — 2004/04/15
@loginame sysname = 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)
—–per campo dbcc
declare @dbcc varchar(255),
@spid_v varchar(3)
——–
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) IN (
'sleeping'
,'background')
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER',
'RESOURCE MONITOR',
'XE TIMER ',
'XE DISPATCHER ',
'LOG WRITER ',
'LOCK MONITOR ',
'SIGNAL HANDLER ',
'TRACE QUEUE TASK',
'BRKR EVENT HNDLR',
'BRKR TASK ',
'CHECKPOINT '
)
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
spid >= @spidlow
and spid <= @spidhigh
—Aggiunta di un campo che proviene dalla dbcc inputbuffer
declare @val int
create table #dbcc (c1 varchar(4000),par int,cmd varchar(4000), spid int)
declare q cursor for
select distinct spid
from #tb1_sysprocesses
open q
fetch next from q into @val
WHILE @@FETCH_STATUS=0
BEGIN
set @spid_v = @val
insert into #dbcc (c1,par,cmd) Exec ('dbcc inputbuffer('+@spid_v+')')

update #dbcc
set spid = @val
where spid is null
FETCH NEXT FROM q INTO @val
END
CLOSE q
DEALLOCATE q
——–Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),a.spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,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(a.cmd,1,' + @charMaxLenCommand + ')
,Sql = b.cmd
,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 + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
from
#tb1_sysprocesses a
left join #dbcc b on –Usually DB qualification is needed in exec().
a.spid = b.spid and
a.spid >= ' + @charspidlow + '
and a.spid <= ' + @charspidhigh + '
–and a.spid > 49 and b.cmd is not null
order by Login, substring(last_batch_char,1,' + @charMaxLenLastBatch + '),a.spid
– (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode — sp_processi

GO