View lock information in SQL Server 2008

; with Tran_locks as (
Select Resource_type,db_name (resource_database_id) as Db_name,resource_description
, object_name (resource_associated_entity_id,resource_database_id) as Object_name,request_mode,request_type, request_status,request_session_id
From sys.dm_tran_locks
where resource_type= ' OBJECT '
Union
Select Resource_type,db_name (resource_database_id) as Db_name,resource_description
, object_name (p.object_id,l.resource_database_id) as Object_name,request_mode,request_type,request_status,request _session_id
From Sys.dm_tran_locks as L
Join Sys.partitions as P on l.resource_associated_entity_id=p.hobt_id
where Resource_type in (' PAGE ', ' KEY ')
Union
Select Resource_type,db_name (resource_database_id) as Db_name,resource_description
, NULL as object_name,request_mode,request_type,request_status,request_session_id
From Sys.dm_tran_locks as L
where Resource_type not in (' OBJECT ', ' PAGE ', ' KEY ')
) Select L.*,s.login_name,text from Tran_locks as L
Join Sys.dm_exec_sessions as S on s.session_id=l.request_session_id
Left join Sys.dm_exec_requests as R on r.session_id=s.session_id
Cross apply Sys.dm_exec_sql_text (sql_handle)
Where not (resource_type= ' DATABASE ' and request_mode= ' S ' and request_type= ' LOCK ' and request_status= ' GRANT ')
--and db_name= '
--and object_name= '
ORDER BY Db_name,object_name,request_type

View lock information in SQL Server 2008