If you have lots of database on a server, that are all used together, say in CROSS database queries, or perhaps they are bolted together via an application or webservice. You are going to need at some point to identify deadlocks or open transactions across the entire server, not just individual databases. The following stored procedure makes that a bit easier.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spShowOpenTransactions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spShowOpenTransactions]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure spShowOpenTransactions
AS
/*
Author: Alex Wilson
Date: 20080923
Version: 1.0
Purpose: Execute DBCC OpenTran For Each Database.
It uses sp_msforeachdb which may be removed from future versions
Usage: exec dba..spShowOpenTransactions
*/
exec master..sp_msforeachdb "print '?' + char(13) + replicate('-', 116); dbcc opentran ('?') with no_infomsgs; print char(13)+char(13) "
GO