Display X Type Locking Users

CREATE PROCEDURE proc_lock
AS

SET NOCOUNT ON

CREATE TABLE #lock
	(spid int,
	dbid int,
	objid int,
	indid int,
	type varchar(10),
	resource varchar(40),
	mode varchar(10),
	status varchar(30))

CREATE TABLE #who
	(spid int,
	 status varchar(30),
	loginame varchar(20),
	hostname varchar(15),
	blk int,
	dbname varchar(20),
	cmd varchar(60))

INSERT INTO #who EXEC sp_who

INSERT INTO #lock EXEC sp_lock


	SELECT 'X Type Lockins'='The user '+w.loginame+'(id:'+CONVERT(VARCHAR(5),w.spid)+') in Server '+w.hostname+' using the Database '+d.name+'(id:'+CONVERT(VARCHAR(5),l.dbid)+') is locking a '+l.type+' with mode '+l.mode
	FROM #who w INNER JOIN #lock l
	ON w.spid=l.spid
	INNER JOIN master..sysdatabases d
	ON d.dbid=l.dbid
	where l.mode like '%X%'


DROP TABLE #who
DROP TABLE #lock