Is there a way, or a stored procedure or query that I can run, to get a list of all the queries/transactions that previously ran on the database and are locking tables.
I am looking into doing an analysis to see which transactions are locking what tables.
It's a more tricky question that it seems.
Historical data regarding locking, as long as we aren't talking about something like "query store" or some other technology that can be thought of as "external", is accumulated by DMV sys.dm_db_index_operational_stats.
This DMV is relatively stable. But its counterpart sys.dm_exec_procedure_stats has a propensity to change as soon as particular plan, which could accumulate some "interesting" data, is recompiled.
This transient property of accumulated data tends to be more pronounced as the rate of change of statistics gets higher. So, you can see that sometimes (or even most of the times) you might not get an exact link between a lock and its cause using DMV from **stats family, but knowing how the omission occurs, you have some information to make good judgment.
Add sys.sql_dependencies linking lock impacted objects and procedures, and it is a starting point.
Please note additional resources for ***stats DMV family:
