Print 'Server and datetime of report'
select @@servername, getdate()

Print 'This will tell you which processes are causing the database blocking on the server.'
Print ''
Print 'If you need to kill any processes, use these spid''s. Make sure you save the report before killing any processes.'
Print ''
set nocount on
SELECT * FROM master..sysprocesses where spid IN (select blocked from master..sysprocesses) and blocked = 0

Print 'Below this is a snapshot of all the SQL processes on the server.'
Print 'Save the report and send to the whole database group.'
Print ''
SELECT * FROM master..sysprocesses

Print 'These are the queries are causing the blocks.'
Print ''

DECLARE @spid int
DECLARE SPID_CURSOR CURSOR FOR 
SELECT spid
FROM master..sysprocesses 
WHERE spid IN (	select blocked 
		from master..sysprocesses) 
AND blocked = 0
FOR READ ONLY

OPEN SPID_CURSOR

FETCH SPID_CURSOR INTO @spid

WHILE @@fetch_status <> -1
BEGIN
	IF @@fetch_status <> -2
	BEGIN
	SELECT '@spid = ' + CONVERT(varchar(20),@spid)
	DBCC INPUTBUFFER (@spid)
	END
	FETCH SPID_CURSOR INTO @spid
END
CLOSE SPID_CURSOR

DEALLOCATE SPID_CURSOR
Print 'These are the processes that are being blocked:'
Print ''
SELECT spid FROM master..sysprocesses where blocked <> 0

SELECT @spid = NULL

Print 'This is the lock info for the blocking processes.'
Print ''

	select 	convert (smallint, req_spid) As spid, 
		rsc_dbid As dbid, 
		rsc_objid As ObjId,
		
		rsc_indid As IndId,
		substring (v.name, 1, 4) As Type,
		substring (rsc_text, 1, 16) as Resource,
		substring (u.name, 1, 8) As Mode,
		substring (x.name, 1, 5) As Status
	into #BlockerLockInfo
	
	from 	master.dbo.syslockinfo,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u
	where   master.dbo.syslockinfo.rsc_type = v.number
			and v.type = 'LR'
			and master.dbo.syslockinfo.req_status = x.number
			and x.type = 'LS'
			and master.dbo.syslockinfo.req_mode + 1 = u.number
			and u.type = 'L'
			and req_spid in (select spid from master..sysprocesses where blocked = 0 and spid IN (select blocked from master..sysprocesses))

DECLARE @dbid int,
	@sqlstring nvarchar(255)

DECLARE DBIDcursor CURSOR FOR 
SELECT DISTINCT dbid 
FROM #BlockerLockInfo
order by dbid
--SELECT *
--FROM #BlockerLockInfo

OPEN DBIDcursor

FETCH DBIDcursor INTO @dbid

WHILE @@fetch_status <> -1
BEGIN
	IF @@fetch_status <> -2
	BEGIN
	
	SELECT @sqlstring = N'SELECT #BlockerLockInfo.*, a.name FROM #BlockerLockInfo , '+name+'..sysobjects a	WHERE dbid = @dbid AND #BlockerLockInfo.ObjID = a.id ORDER BY spid' from master..sysdatabases where dbid = @dbid
	--SELECT @sqlstring 	
	EXEC sp_executesql @sqlstring, N'@dbid int', @dbid
	END
	FETCH DBIDcursor INTO @dbid

END
CLOSE DBIDcursor
DEALLOCATE DBIDcursor
drop table #BlockerLockInfo