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

CREATE VIEW dbo.ViewBlocks
AS
WITH tbl1
	 AS (SELECT t1.spid,
				t1.blocked,
				SQLtext.text
		 FROM [master].sys.sysprocesses t1
		 CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) SQLtext
		),
	 tbl2
	 AS (
	 SELECT tbl1.spid,
			tbl1.blocked,
			tbl1.spid head_session_id,
			tbl1.text
	 FROM tbl1 tbl1
	 WHERE tbl1.blocked = 0
	 UNION ALL
	 SELECT tbl1.spid,
			tbl1.blocked,
			tbl2.head_session_id,
			tbl1.text
	 FROM tbl1 tbl1
	 INNER JOIN tbl2 ON tbl2.spid = tbl1.blocked)
	 SELECT tbl2.spid,
			tbl2.blocked,
			case WHEN tbl2.head_session_id = tbl2.spid then 0 else tbl2.head_session_id END head_session_id,
			tbl2.text
	 FROM tbl2 tbl2;

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