I realize this is an old thread, but wanted to add.
The query is great, thanks Ian.
On one of my systems it was leaving out some open transactions so I made a tweak:
FROM sys.dm_tran_session_transactions AS tst
INNER JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt
ON tst.transaction_id = tdt.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
INNER JOIN sys.dm_exec_connections conns ON tst.session_id = conns.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(conns.most_recent_sql_handle) TXT