if exists (select * from sysobjects where id = object_id('dbo.sp_hywho') and sysstat & 0xf = 4)
	drop procedure dbo.sp_hywho
GO

Create Procedure sp_hywho
( 	@vcDBName sysname = NULL )
AS

SET NOCOUNT ON

IF EXISTS ( SELECT 1
	FROM	master..sysprocesses p
	WHERE	p.blocked = 0 
	AND	EXISTS ( SELECT 1
		FROM	master..sysprocesses p2
		WHERE	p2.blocked = p.spid ) )
BEGIN
	PRINT "Blocking caused by:"
	PRINT ""
	SELECT	p.spid
	,convert(char(12), d.name) db_name
	, program_name
	, convert(char(12), l.name) login_name
	, convert(char(12), hostname) hostname
	, cmd           
	, p.status        
	, p.blocked
	, login_time                  
	, last_batch 
	, p.spid
	FROM	master..sysprocesses p
	JOIN	master..sysdatabases d ON p.dbid =  d.dbid 
	JOIN	master..syslogins l ON p.suid = l.suid 
	WHERE	p.blocked = 0 
	AND	EXISTS ( SELECT 1
		FROM	master..sysprocesses p2
		WHERE	p2.blocked = p.spid ) 
	AND	(p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
	ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5
	PRINT ""
END

SELECT	p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd           
, p.status        
, p.blocked
, login_time                  
, last_batch 
, p.spid
FROM	master..sysprocesses p
JOIN	master..sysdatabases d ON p.dbid =  d.dbid 
JOIN	master..syslogins l ON p.suid = l.suid 
WHERE	(p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5


SET NOCOUNT OFF

GO

if exists (select * from sysobjects where id = object_id('dbo.sp_hywho') and sysstat & 0xf = 4)
	GRANT EXEC ON dbo.sp_hywho TO PUBLIC
GO