检查锁定SQLServer数据库的ProcessID

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