通过存储过程来查询SQL Server中，是否存储在阻塞和死锁进程。

原理如下：

1、查询master..sysprocesses中blocked不为0的系统进程
2、通过sys.dm_exec_sql_text来查找阻塞或死锁的进程，其对应的SQL语句

完整代码如下：

CREATE  PROCEDURE 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 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  N'现在没有阻塞和死锁信息' 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 N'阻塞根源' + 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)) + N'被' + CAST(@bl AS VARCHAR(10)) + N'阻塞' 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
GO