SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hywho2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_hywho2]
GO

Create Procedure dbo.sp_hywho2
( 	@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.sid = l.sid 
	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.sid = l.sid 
WHERE	(p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5


SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hywho2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXEC ON dbo.sp_hywho2 TO PUBLIC
GO