SP to display locking users in tree format

/*
PART ONE:USER DEFINED FUNCTION dbo.UFN_GETLOCKSTREE
RUN THIS FIRST
*/

/*****************************************************************************//**      Object:  User Defined Function dbo.UFN_GETLOCKSTREE******//** Script Date:  07/23/2002 10:21:32******//**  Author:  Keith Mac Lure******//**Purpose:  Used in conjuction with USP_LOCKTREE. Accepts @BLKID******//**  and recurses through tree listing to determine level  ******//**  and path.******//*****************************************************************************/
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[UFN_GETLOCKSTREE]') AND XTYPE IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [DBO].[UFN_GETLOCKSTREE]
GO

CREATE FUNCTION UFN_GETLOCKSTREE
(
  @BLKID AS INT
)

RETURNS @TREE TABLE    --Uses Table Data type to return results
(
  SPID   INT          NOT NULL,--SPID of process being blocked by @BLKID
  BLOCKED   INT          NULL,--ID of process blocking SPID
  HOSTNAME VARCHAR(25)  NOT NULL,--SQL Hostname i.e. username
  LVL     INT          NOT NULL,--Used for recursion
  PATH    VARCHAR(900) NOT NULL--Used to display tree level
)
AS

BEGIN
  
  DECLARE @LVL AS INT, @PATH AS VARCHAR(1000)
  SELECT @LVL = 0, @PATH = '.'

  INSERT INTO @TREE
    SELECT SPID, BLOCKED, HOSTNAME,
           @LVL, '.' + CAST(SPID AS VARCHAR(10)) + '.'
    FROM MASTER..LOCKS--MASTERS..LOCKS is created by the Calling USP
    WHERE SPID = @BLKID

  WHILE @@ROWCOUNT > 0
  BEGIN
    SET @LVL = @LVL + 1

    INSERT INTO @TREE
      SELECT E.SPID, E.BLOCKED, E.HOSTNAME,
             @LVL, T.PATH + CAST(E.SPID AS VARCHAR(10)) + '.'
      FROM MASTER..LOCKS AS E JOIN @TREE AS T
        ON  E.BLOCKED = T.SPID AND T.LVL = @LVL - 1
  END  
  
  RETURN

END
GO


/*
PART TWO:USER DEFINED STORED PROCEDURE dbo.USP_LOCKTREE
RUN THIS SECOND
*/

/*****************************************************************************//**      Object:  USER DEFINED STORED PROCEDURE dbo.USP_LOCKTREE******//** Script Date:  07/23/2002 10:25:40******//**  Author:  Keith Mac Lure******//**Purpose:  Displays a "locking chain" in tree format. Creates******//**  MASTER..LOCKS Table with info on locking processes,  ******//**  calls dbo.UFN_GETLOCKSTREE which recurses locking ******//**  info and creates locking tree.******//*****************************************************************************/
USE MASTER
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[USP_LOCKTREE]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE DBO.USP_LOCKTREE
GO

CREATE PROCEDURE USP_LOCKTREE
AS
SET NOCOUNT ON
  BEGIN
    SELECT P.SPID, P.BLOCKED, RTRIM(P.HOSTNAME) HOSTNAME
  INTO MASTER..LOCKS
      FROM MASTER..SYSPROCESSES P 
  WHERE P.BLOCKED <> 0
        OR SPID IN (SELECT BLOCKED FROM MASTER..SYSPROCESSES)
          AND BLOCKED=0
      ORDER BY P.BLOCKED, P.SPID

    DECLARE @COUNT INT
    SET @COUNT = (SELECT SPID FROM MASTER..LOCKS WHERE BLOCKED = 0)

    SELECT ' ' + REPLICATE ('   ', LVL) + RTRIM(HOSTNAME)+ ' ('+ CAST(SPID AS VARCHAR)+')' AS "BLOCKING LOCKS"
      FROM UFN_GETLOCKSTREE(@COUNT)
      ORDER BY PATH

    DROP TABLE MASTER..LOCKS

  END

SET NOCOUNT OFF
GO