SQL 查看被锁表和解锁

查看被锁表：

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
 FROM sys.dm_tran_locks
WHERE resource_type='OBJECT' and OBJECT_NAME(resource_associated_entity_id) IS NOT NULL

spid   锁表进程 

tableName   被锁表名

解锁：

declare @spid  int 

Set @spid  = 57 --锁表进程

declare @sql varchar(50)

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

exec(@sql)

一次性解锁多个进程占用的某张表

DECLARE @tableName NVARCHAR(50)='Ledger_ZZJY_school';
IF object_id('tempdb..#tempDB') is not null 
	BEGIN
		DROP table #tempDB
	END	
SELECT   request_session_id  spid,CONVERT(NVARCHAR(50),OBJECT_NAME(resource_associated_entity_id)) tableName  INTO #tempDB
from   sys.dm_tran_locks 
WHERE resource_type='OBJECT';
DECLARE @spid INT,@sql varchar(50);

DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT DISTINCT spid FROM #tempDB WHERE tableName=@tableName) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
	FETCH NEXT FROM My_Cursor  INTO @spid;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @sql='kill '+cast(@spid  as varchar)
		exec(@sql)
		FETCH NEXT FROM My_Cursor  INTO @spid;
	END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标