USE [DB]
GO
/****** Object: StoredProcedure [dbo].[sp_who_lock] Script Date: 2024/7/18 13:37:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
DECLARE @spid INT ,
@bl INT ,
@intTransactionCountOnEntry INT ,
@intRowcount INT ,
@intCountProperties INT ,
@intCounter INT,
@sql_handle VARBINARY(64)
DECLARE @tmp_lock_who TABLE
(
id INT IDENTITY(1, 1) ,
spid SMALLINT ,
bl SMALLINT,
sql_handle VARBINARY(64)
)
IF @@ERROR <> 0
RETURN @@ERROR
;
WITH tb_blocked AS(
SELECT DISTINCT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0
)
INSERT INTO @tmp_lock_who
( spid ,
bl, sql_handle
)
SELECT DISTINCT blocked,0, p_bl.sql_handle
FROM tb_blocked
CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
WHERE NOT EXISTS ( SELECT *
FROM tb_blocked a
WHERE tb_blocked.blocked = a.spid )
UNION ALL
SELECT spid, blocked, sql_handle FROM tb_blocked
IF @@ERROR <> 0
RETURN @@ERROR
-- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*),
@intCounter = 1
FROM @tmp_lock_who
IF @@ERROR <> 0
RETURN @@ERROR
IF @intCountProperties = 0
SELECT '现在没有阻塞和死锁信息' AS message
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl, @sql_handle = sql_handle
FROM @tmp_lock_who
WHERE id = @intCounter
BEGIN
IF @bl = 0
BEGIN
SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
ELSE
BEGIN
SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
END
DBCC INPUTBUFFER(@spid)
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
RETURN 0
END
--根据ID 查看堵塞信息
SELECT * FROM sys.sysprocesses WHERE spid =1204
--查看当时SPID执行的sql语句
DBCC inputbuffer(162);