Getting All SQL Corresponding to Locks I was recently involved in debugging a tricky SQL Server locking issue.  We eventually figured out what was going on by studying the application code, but it would have been much easier if SQL Server had provided more information.  I'm wondering if there's a way to get that information.  Here's a simple reproduction:
create table Table1 (k int primary key)
create table Table2 (k int primary key)
go

begin transaction
insert into Table1 values (1)  -- acquire a lock
go

insert into Table2 values (2)  -- acquire a second lock
go

I don't show it, but the application was committing the transaction, as it should have.  Before the commit would happen though, it would cause blocking, and our debugging tools were only reporting the text of the last batch/statement executed--the insert into Table2 in this case--along with locks corresponding to a completely different object.  (Table1 in my example.)
Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?
Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?

Only in XEvents/Trace by capturing the begin/end statement and acquisition and release of the locks. And that is a very verbose thing to trace, so you would rarely turn on such a trace on a production system.
sys.dm_tran_locks lists all the locks, but there's no DMV that stores the statement that caused the lock to be acquired.