select count(*) from master.dbo.sysprocesses where sid <> 0X01

CREATE procedure sp_ProcessInfo
as
set nocount on

declare @loop int , @MaxRecs int , @stmt varchar(255) , @sql varchar(100)

create table #stmt (EventType varchar(255) , parameters int , Eventinfo varchar(255) )

create table #processes ( Rowid int identity(1,1) primary key clustered not null,
dbid smallint, DatabaseName varchar(15), Program_Name varchar(30), spid smallint, blocked smallint
, last_batch datetime, open_tran smallint, waittime int, 
HostName varchar(20), NT_Username varchar(20), Login_Name varchar(20), Status varchar(20), Cmd varchar(20),
LastWaitType varchar(20), WaitResource varchar(20), Physical_IO int, 
MemUsage int, uid smallint, cpu int, 
 SQL_Stmt_255 varchar(255)
)

INSERT #processes (
dbid , DatabaseName , spid , blocked , last_batch , 
open_tran , waittime , LastWaitType , WaitResource , Physical_IO , 
MemUsage , uid , cpu , HostName , Program_Name , NT_Username , 
Login_Name , Status , Cmd 
)
Select p.dbid , Substring(d.name,1,15) As DatabaseName , p.spid , p.blocked 
, p.last_batch , p.open_tran , p.waittime, Substring(p.LastWaitType,1,20) As LastWaitType 
, Substring(p.WaitResource,1,20) As WaitResource , p.Physical_IO , p.MemUsage , p.uid 
, p.cpu , Substring(p.hostname,1,20) As HostName , Substring(p.program_name,1,30) As Program_Name 
, Substring(p.nt_username,1,20) as NT_Username , Substring(p.loginame,1,20) As Login_Name 
, Substring(p.Status,1,20) As Status , Substring(p.Cmd,1,20) as Cmd
from master..sysprocesses as p with (nolock) 
inner join master..sysdatabases as d with (nolock) 
on p.dbid = d.dbid 
--order by d.name , p.Last_Batch , p.spid
order by case when p.blocked > 0 then -1 else 0 end ,  p.Last_Batch ,  d.name , p.program_name ,p.spid


select @loop = 0 , @MaxRecs = @@rowcount , @stmt = '' , @sql = ''


while @loop < @MaxRecs
begin
	truncate table #stmt

	Select @loop = @loop + 1
	
	select @sql = 'dbcc inputbuffer(' + convert(varchar(10), spid) + ')' from #processes where rowid = @loop

	--print @sql
	insert #stmt
	exec (@sql)

	Update #processes
	set SQL_Stmt_255 = Replace(b.Eventinfo,char(13)+char(10), ' ')
	from #processes a, #stmt b
	where RowID = @loop
end


select * from #processes 
order by rowid
	
drop table #processes , #stmt
GO