SELECT * from [master].[dbo].[sysprocesses]
WHERE blocked > 0

select * from sys.dm_tran_session_transactions where session_id = @blocking_session_id;

USE master;

select
t1.resource_type as [lock type]
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- this is the statement executing right now
,t2.blocking_session_id as [blocker sid] -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address;
GO

DECLARE deleteCursor CURSOR FAST_FORWARD FOR 
 select name from @tblList order by level, name

  OPEN deleteCursor

 FETCH NEXT FROM deleteCursor 
  INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

....
....

....
....

    FETCH NEXT FROM deleteCursor 
	INTO @name
	
END 

CLOSE deleteCursor
DEALLOCATE deleteCursor