USE master
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_nrSpidByStatus
@status varchar(20) = 'runnable'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else
	begin
		insert	#spids 
                select spid from master..sysprocesses 
                  where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids

	while @spid < @maxSpid
	begin
		select	@spid = min(spid) from #spids where spid > @spid

		select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'

		delete #temp

		insert #temp
		exec (@cmd)

		select 	@id = 0 ,
			@buf = ''
		select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')

		from #temp

		insert 	#spid
		select	@spid, @buf
	end

	select 	blk = case 
          when s.blocked <> 0 then convert(varchar(3),s.blocked) 
          else '   ' 
          end ,
		spid = convert(varchar(4),#spid.spid) ,
		s.physical_io ,
		status = left(s.status,12) ,
		last_batch = convert(varchar(23),s.last_batch,121) ,
		s.cmd ,
		#spid.command ,
		login_time = convert(varchar(23),s.login_time,121) ,
		s.HostName
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	and 	(ecid = 0 or isnumeric(@status) = 1)
	order by s.status, #spid.spid

	drop table #spid
	drop table #temp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO