T-SQL script to find out which table is locked

This is a handy script and it is common in some scenarios that you get your tabled locked due to some deadlock condition and results in unavailability of such table in whole application. There are other ways to find out e.g: using SSMS can give you handful details of what you want but in case if you want some custom parameters with custom query you will have to understand which tables MS SQL server provides such information. There are tables in sys called dm_tran_locks that shows how many translations running in current system this tables gives us a lot of information but most of it is ids so to get descriptive values you will have to join these with other sys tables like 'objects', 'waiting_tasks' etc.. Hope this will help other as well.

SELECT t1.resource_type,
       t1.resource_database_id,
       t1.resource_associated_entity_id,
       t1.request_mode,
       t1.request_session_id,
       t2.blocking_session_id,
       o1.name         'object name',
       o1.type_desc    'object descr',
       p1.partition_id 'partition id',
       p1.rows         'partition/page rows',
       a1.type_desc    'index descr',
       a1.container_id 'index/page container_id'
FROM   sys.dm_tran_locks AS t1
       INNER JOIN sys.dm_os_waiting_tasks AS t2
               ON t1.lock_owner_address = t2.resource_address
       LEFT OUTER JOIN sys.objects o1
                    ON o1.object_id = t1.resource_associated_entity_id
       LEFT OUTER JOIN sys.partitions p1
                    ON p1.hobt_id = t1.resource_associated_entity_id
       LEFT OUTER JOIN sys.allocation_units a1
                    ON a1.allocation_unit_id = t1.resource_associated_entity_id