SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_lock2]
(
@dbname sysname = NULL,
@spid INT = NULL
)
AS
/*************************************************************************************************
		Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
 
Purpose:	To display detailed lock information
 
Written by:	Narayana Vyas Kondreddi
		http://vyaskn.tripod.com
 
Tested on: 	SQL Server 7.0 and SQL Server 2000
 
Date modified:	August-13-2001 12:00 AM
 
Email: 		vyaskn@hotmail.com
 
Examples:
 
To see all the locks:
EXEC sp_lock2
 
To see all the locks in a particular database, say 'pubs':
EXEC sp_lock2 pubs
 
To see all the locks held by a particular spid, say 53:
EXEC sp_lock2 @spid = 53
 
To see all the locks held by a particular spid (23), in a particular database (pubs):
EXEC sp_lock2 pubs, 23
*************************************************************************************************/
 
BEGIN
SET NOCOUNT ON
CREATE TABLE #lock
(
	spid INT,
	dbid INT,
	ObjId INT,
	IndId INT,
	TYPE CHAR(30),
	Resource CHAR(60),
	Mode CHAR(30),
	STATUS CHAR(50)
)
 
INSERT INTO #lock EXEC sp_lock
 
--select spid from sp_lock
 
IF @dbname IS NULL
BEGIN
	IF @spid IS NULL
	BEGIN
		SELECT a.spid AS SPID, 
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
		db_name(dbid) AS [DATABASE Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, TYPE, Resource, Mode, STATUS
		FROM #lock a
 
 
	END
	ELSE
	BEGIN
		SELECT a.spid AS SPID, 
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],	
		db_name(dbid) AS [DATABASE Name], ISNULL(object_name(ObjId),'') AS [Object Name],IndId, TYPE, Resource, Mode, STATUS
		FROM #lock a
		WHERE spid = @spid
	END
END
ELSE
BEGIN
	IF @spid IS NULL 
	BEGIN
		SELECT a.spid AS SPID,
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],		
		ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId, 
		ISNULL((SELECT name FROM sysindexes WHERE id = a.objid AND indid = a.indid ),'') AS [INDEX Name],
		a.Type, a.Resource, a.Mode, a.Status
		FROM #lock a
		WHERE dbid = db_id(@dbname)
	END
	ELSE
	BEGIN
		SELECT a.spid AS SPID,
		(SELECT DISTINCT program_name FROM master..sysprocesses WHERE spid = a.spid) AS [Program Name],
		ISNULL(object_name(a.ObjId),'') AS [Object Name],a.IndId, 
		ISNULL((SELECT name FROM sysindexes WHERE id = a.objid AND indid = a.indid ),'') AS [INDEX Name],
		a.Type, a.Resource, a.Mode, a.Status
		FROM #lock a
		WHERE dbid = db_id(@dbname) AND spid = @spid			
	END
END
 
DROP TABLE #lock
 
END