查詢被鎖的表
SELECT
request_session_id spid,
OBJECT_NAME( resource_associated_entity_id ) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'
解鎖表
DECLARE @spid INT
SET @spid = 113 --鎖表進(jìn)程
DECLARE
@SQL VARCHAR ( 1000 )
SET @SQL = 'kill ' + CAST ( @spid AS VARCHAR )
EXEC ( @SQL )
通過(guò)游標(biāo)循環(huán)解鎖
-- 釋放游標(biāo)（假如有重名有表的話使用）
DEALLOCATE order_cursor
-- 游標(biāo)循環(huán)遍歷
BEGIN
-- 定義循環(huán)的變量
DECLARE
@a INT,@error INT,@temp INT
SET @a = 1
SET @error = 0
-- 定義游標(biāo)
DECLARE
order_cursor CURSOR FOR (SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT' AND resource_associated_entity_id = '495340829')
/* 注意：WHERE條件使用OBJECT_NAME( resource_associated_entity_id ) 會(huì)報(bào)express轉(zhuǎn)int錯(cuò)誤瞒渠；
這里需要把【表名:TBTHD_PERINFO_ALL】查出來(lái)再使用皮璧，即'495340829'
*/
-- 打開游標(biāo)
OPEN order_cursor
-- 開始循環(huán)游標(biāo)變量
FETCH NEXT FROM order_cursor INTO @temp
WHILE
@@FETCH_STATUS = 0 -- 返回被 FETCH語(yǔ)句執(zhí)行的最后游標(biāo)的狀態(tài)
BEGIN
-- 解鎖表
DECLARE
@SQL VARCHAR ( 1000 )
SET @SQL = 'kill ' + CAST ( @temp AS VARCHAR )
EXEC ( @SQL )
SET @a =@a + 1
SET @error = @error + @@ERROR -- 記錄每次運(yùn)行sql后是否正確，0正確
FETCH NEXT
FROM
order_cursor INTO @temp -- 轉(zhuǎn)到下一個(gè)游標(biāo)遥巴，沒(méi)有會(huì)死循環(huán)
END CLOSE order_cursor -- 關(guān)閉游標(biāo)
DEALLOCATE order_cursor -- 釋放游標(biāo)
END
GO
