Tipsy little box

sys.dm_tran_locks (Transact-SQL): Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

(sursa: http://msdn.microsoft.com/en-us/library/ms190345.aspx)

Lock Modes: http://msdn.microsoft.com/en-us/library/ms175519(SQL.90).aspx

The following query will display lock information. The value for should be replaced with the database_id from sys.databases.
SELECT * FROM  sys.databases OR if you already know the Id you can use SELECT db_name(<dbid>)

SELECT resource_type,request_mode,request_lifetime,resource_associated_entity_id,
(SELECT [name] FROM sys.objects WHERE object_id=(SELECT object_id from sys.partitions
WHERE hobt_id = resource_associated_entity_id)),
(SELECT [name] FROM sys.objects WHERE object_id= resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE resource_database_id= <bdid>

The following query returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.

SELECT object_name(object_id), *
FROM sys.partitions
WHERE  hobt_id= <resource_associated_entity_id>

The following query will also show blocking information.
SELECT t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_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