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