if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrLocks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrLocks]
GO

Create procedure sp_nrLocks
@spidIn		int = 0 ,
@type		varchar(10) = 'locks' ,
@maxlocks	int = 10000
as
/*
exec sp_nrlocks -- display all locks held
exec sp_nrLocks 10  -- display all locks held by spid 10
exec sp_nrLocks 0, 'Spids'  -- display last command executed by all spids
*/

set transaction isolation level read uncommitted
set nocount on

declare	@cmd varchar(1000)

if @type = 'Locks' goto Locks
if @type = 'Spids' goto Spids

Spids:
declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	select 	@spid = @spidIn ,
		@maxSpid = case when @spidIn = 0 then max(spid) else @spid end
	from	master..sysprocesses

	while @spid < @maxSpid
	begin
		select 	@spid = min(spid)
		from	master..sysprocesses
		where	spid > @spid

		select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'

		delete #temp

		insert #temp
		exec (@cmd)

		select 	@id = 0 ,
			@buf = ''
		select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')

		from #temp

		insert 	#spid
		select	@spid, @buf
	end

	select 	#spid.spid ,
		s.physical_io ,
		status = left(s.status,12) ,
		s.login_time ,
		last_batch ,
		#spid.command
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	order by #spid.spid

	drop table #spid
	drop table #temp

	if @type <> 'All'
		return

Locks:
	set 	@spid = @spidIn

	select 	physical_io ,
		login_time ,
		last_batch ,
		cmd ,
		status ,
		*
	from master..sysprocesses
	where spid = @spid or @spid = 0
	order by spid


declare	@NumLocks int
	create table #SpidLocks (spid int, NumLocks int)
	if @spid = 0
		insert	#SpidLocks
		select	spid, count(*)
		from master..syslocks
		group by spid
	else
		insert	#SpidLocks
		select	spid, count(*)
		from	master..syslocks
		where	spid = @spid
		group by spid

	select @Numlocks = sum(Numlocks) from #SpidLocks

	if @Numlocks > @MaxLocks
	begin
		select 	'Max locks = '
			+ convert(varchar(10),@maxlocks)
			+ ' less than number locks for spids = '
			+ convert(varchar(10),@Numlocks)

		select	*
		from	#SpidLocks
		order by spid

		return
	end
	drop table #SpidLocks

	create table #a
		(
		spid		int ,
		dbname		varchar(128) ,
		NumberLocks	int ,
		id		int ,
		ObjectName	varchar(128) null
		)

	insert	#a
		(
		spid ,
		dbname ,
		NumberLocks ,
		id ,
		ObjectName
		)
	select	spid ,
		db_name(dbid) ,
		count(*) ,
		id ,
		null
	from	master..syslocks
	where	(spid = @spid or @spid = 0)
	group by spid, db_name(dbid), id

declare	@DBName varchar(128)

	select @DBName = ''
	while exists(select * from #a where @DBName < dbname)
	begin
		select 	@DBName = min(dbname)
		from	#a
		where	@DBName < dbname

		select @cmd = 'use ' + @DBName + ' update #a set ObjectName = object_name(id) where dbname = ''' + @DBName + ''''
		exec (@cmd)
	end

	select 	spid ,
		NumberLocks = count(*)
	from 	master..syslocks
	where	spid = @spid or @spid = 0
	group by spid

	select 	spid ,
		Object = left(coalesce(dbname,'') + space(20),20) + '.' + left(coalesce(ObjectName,'') + space(30), 30) ,
		NumberLocks ,
		ObjectID = id ,
		dbname ,
		ObjectName
	from	#a
	order by spid, case when ObjectName is null then 2 else 1 end, Object

	drop table #a

	if @type <> 'All'
		return
go