SP to display locking users in tree format

,

This SP will give a listing of users blocking and being blocked in a tree formation (similar to explorer tree list). It uses a User defined function that recurses through the locking information gathered from sysprocesses. The listing only shows the Hostname (username) and the SPID.
First create the User Defined Function (Part One) then create the (U)SP. Usage "Exec USP_LOCKTREE" (sans quotes).

/*
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

Rate

2 (1)

Share

Share

Rate

2 (1)