SQL SERVER 查询被锁表

--查询被锁表

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName    

from   sys.dm_tran_locks where resource_type='OBJECT'

declare @spid  int  

--循环所有实验室数据

DECLARE Spid_Cursor CURSOR --定义游标

FOR (select request_session_id spid

from sys.dm_tran_locks where resource_type='OBJECT' ) --查询出被锁表spid

OPEN Spid_Cursor; --打开游标

FETCH NEXT FROM Spid_Cursor INTO @spid; --读取第一行数据

WHILE @@FETCH_STATUS = 0

 BEGIN  

declare @sql varchar(1000)

set @sql='kill '+cast(@spid  as varchar)

exec(@sql)

  FETCH NEXT FROM Spid_Cursor INTO @spid; --读取下一行数据

 END

CLOSE Spid_Cursor; --关闭游标

DEALLOCATE Spid_Cursor; --释放游标