IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_Lock_Scan')
DROP PROCEDURE sp_Lock_Scan
GO
CREATE PROCEDURE sp_Lock_Scan
AS
DECLARE @SPID INT
DECLARE @BLK INT
DECLARE @Count INT
DECLARE @Counter INT
DECLARE @LOCK BIT
CREATE TABLE #Temp
(
[Id] INT IDENTITY
,[SPID] INT
,[BLOCK] INT
)
SELECT @LOCK = 0
IF @@ERROR <> 0 RETURN @@ERROR
INSERT INTO #Temp
(
[SPID], [BLOCK]
)
SELECT
0, [blocked]
FROM
(
SELECT * FROM [master]..[sysprocesses] WHERE [blocked] > 0
) a
WHERE
NOT EXISTS
(
SELECT * FROM [master]..[sysprocesses] WHERE a.[blocked] = [spid] AND [blocked] > 0
)
UNION
SELECT [spid], [blocked] FROM [master]..[sysprocesses] WHERE [blocked] > 0
IF @@ERROR <> 0 RETURN @@ERROR
SELECT @Count = COUNT(*), @Counter = 1 FROM #Temp
IF @@ERROR <> 0 RETURN @@ERROR
IF @Count = 0
BEGIN
SELECT N'没有阻塞和死锁信息' [ScanMessage]
RETURN 0
END
ELSE
BEGIN
WHILE @Counter <= @Count
BEGIN
IF EXISTS
(
SELECT * FROM #Temp a
WHERE
a.[Id] > @Counter
AND
EXISTS
(
SELECT * FROM #Temp WHERE [Id] <= @Counter AND a.[BLOCK] = [SPID]
)
)
BEGIN
SELECT @LOCK = 1
SELECT @SPID = [SPID], @BLK = [BLOCK] from #Temp WHERE [Id] = @Counter
SELECT N'引起数据库死锁的是：【' + CAST(@SPID AS NVARCHAR(255)) + N'】进程,其执行的SQL语言如下' [ScanMessage]
SELECT @SPID [SPID], @BLK [BLOCKED]
DBCC INPUTBUFFER(@SPID)
DBCC INPUTBUFFER(@BLK)
END
SELECT @Counter = @Counter + 1
END
IF @LOCK = 0
BEGIN
SELECT @Counter = 1
WHILE @Counter <= @Count
BEGIN
SELECT @SPID = [SPID], @BLK = [BLOCK] FROM #Temp where [Id] = @Counter
IF @SPID = 0
SELECT N'引起阻塞的是：【'+ CAST(@BLK AS NVARCHAR(255)) + '】，其执行的SQL语法如下' [ScanMessage]
ELSE
SELECT N'进程【' + CAST(@SPID AS NVARCHAR(255)) + N'】被进程【' + CAST(@BLK AS NVARCHAR(255)) + N'】阻塞，当前进程的SQL语法如下' [ScanMessage]
DBCC INPUTBUFFER(@SPID)
DBCC INPUTBUFFER(@BLK)
SELECT @Counter = @Counter + 1
END
END
END
RETURN 0
GO
