The next code can be modified to check any other kind of lock's, this is usefull when we are gathering information of T-SQL Code or transactions being processed by the server.
SELECT
convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
OBJECT_NAME (rsc_objid) ObjectName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
FROM
master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
WHERE master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_objid >0
and substring (u.name, 1, 8) like '%X%'
