- CREATE PROCEDURE #sp_who_lock
- AS
- BEGIN
- DECLARE @spid INT
- DECLARE @blk INT
- DECLARE @count INT
- DECLARE @index INT
- DECLARE @lock TINYINT
- SET @lock = 0
- DECLARE @temp_who_lock AS TABLE (
- id INT identity(1, 1),
- spid INT,
- blk INT
- )
- IF @@error <> 0
- RETURN @@error
- INSERT INTO @temp_who_lock (
- spid,
- blk
- )
- SELECT 0,
- blocked
- FROM (
- SELECT *
- FROM master..sysprocesses
- WHERE blocked > 0
- ) a
- WHERE NOT EXISTS (
- SELECT TOP 1 1
- 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(1),
- @index = 1
- FROM @temp_who_lock
- IF @@error <> 0
- RETURN @@error
- IF @count = 0
- BEGIN
- SELECT N'没有阻塞和死锁信息'
- RETURN 0
- END
- WHILE @index <= @count
- BEGIN
- IF EXISTS (
- SELECT TOP 1 1
- FROM @temp_who_lock a
- WHERE id > @index
- AND EXISTS (
- SELECT TOP 1 1
- FROM @temp_who_lock
- WHERE id <= @index
- AND a.blk = spid
- )
- )
- BEGIN
- SET @lock = 1
- SELECT @spid = spid,
- @blk = blk
- FROM @temp_who_lock
- WHERE id = @index
- SELECT N'引起数据库死锁的是:' + CAST(@spid AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下'
- SELECT @spid,
- @blk
- DBCC INPUTBUFFER (@spid)
- DBCC INPUTBUFFER (@blk)
- END
- SET @index = @index + 1
- END
- IF @lock = 0
- BEGIN
- SET @index = 1
- WHILE @index <= @count
- BEGIN
- SELECT @spid = spid,
- @blk = blk
- FROM @temp_who_lock
- WHERE id = @index
- IF @spid = 0
- SELECT N'引起阻塞的是:' + CAST(@blk AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下'
- ELSE
- SELECT N'进程号SPID:' + CAST(@spid AS NVARCHAR(10)) + N'被进程号SPID:' + CAST(@blk AS NVARCHAR(10)) + N'阻塞,其当前进程执行的SQL语法如下'
- DBCC INPUTBUFFER (@spid)
- DBCC INPUTBUFFER (@blk)
- SET @index = @index + 1
- END
- END
- RETURN 0
- END
- GO
- EXEC #sp_who_lock
- --该片段来自于http://www.codesnippet.cn/detail/1108201513415.html
来源: http://www.codesnippet.cn/detail/1108201513415.html