The script shows all locks and the responsible objects.
IF OBJECT_ID('tempdb..#temp_locks') IS NOT NULL
drop table #temp_locks
IF OBJECT_ID('tempdb..#temp_objname') IS NOT NULL
drop table #temp_objname
create table #temp_locks
(
[spid] smallint ,
login_name varchar(200),
[dbid] smallint,
dbname varchar(200),
schemaName varchar(200),
[ObjID] int,
objName varchar(200),
[IndId] smallint,
[Type] nchar(4),
[Resource] nchar(32) ,
Mode nvarchar(8),
[Status] nvarchar(5)
)
create table #temp_objname
(
dbname varchar(200),
schemaName varchar(200),
[ObjID] int,
objName varchar(200)
)
insert into #temp_locks
(
[spid],
[dbid],
[ObjID],
[IndId],
[Type],
[Resource],
Mode,
[Status]
)
exec sp_lock
update #temp_locks
set dbname = dbs.name,login_name = process.loginame
from #temp_locks locks
left join sys.databases dbs
on dbs.database_id = locks.dbid
left join sys.sysprocesses process
on process.spid = locks.spid
declare tbl_cursor cursor for select dbname, [ObjID] from #temp_locks
declare @db_name varchar(200)
declare @obj_id int
open tbl_cursor
fetch next from tbl_cursor into @db_name, @obj_id
while @@FETCH_STATUS = 0
begin
from ' +@db_name+'.sys.all_objects o join ' +@db_name+'.sys.schemas s
on o.schema_id = s.schema_id
where o.object_id = '+ convert(varchar(50), @obj_id)
--print @cmd
insert into #temp_objname
exec (@cmd)
fetch next from tbl_cursor into @db_name, @obj_id
end
close tbl_cursor
deallocate tbl_cursor
update #temp_locks
set objName = names.objName,schemaName = names.schemaName
from #temp_locks locks
join #temp_objname names
on locks.dbname = names.dbname
and locks.ObjID = names.ObjID
select
spid,login_name as [login],
dbname as [database],
schemaName as [schema],
objName as [object],
[Resource], [mode], [Status]
from #temp_locks
drop table #temp_locks
drop table #temp_objname