sp_lock2  = sp_lock +shows database and object name

CREATE procedure sp_lock2
AS
BEGIN
SET NOCOUNT ON

DECLARE @test cursor
DECLARE @dbname nvarchar(60)
DECLARE @strsql varchar(300)
DECLARE @objid int, @id int, @indid int


CREATE TABLE #splock2_table (
[id] INT NOT NULL IDENTITY(1,1),
[spid] [smallint] NULL ,
[dbid] smallint not null,
[dbName] [nvarchar] (30) ,
[ObjId] [int] NOT NULL ,
[objname] nvarchar(60) NULL,
[IndId] [smallint] NOT NULL ,
[indName] nvarchar(60) NULL,
[Type] [nvarchar] (4)  NULL ,
[Resource] [nvarchar] (16)  NULL ,
[Mode] [nvarchar] (8)  NULL ,
[Status] [nvarchar] (5) NULL 
) 

CREATE INDEX INX_SP_LOCK2_OBJID on #splock2_table ( objid )

INSERT INTO #splock2_table ( spid, dbid, dbname, objid, indid, type, resource, mode, status ) 
-- the base for the following query is taken from sp_lock
(SELECT convert (smallint, req_spid) As spid,
rsc_dbid as dbid,
left( d.name, 30) as dbName,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from 
master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysdatabases d

where   master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and d.dbid = rsc_dbid )


SET @test =  CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT [id],dbname,objid,indid FROM #splock2_table WHERE objid > 0 

OPEN @test
FETCH NEXT FROM @test INTO @id, @dbname, @objid, @indid
WHILE @@fetch_status = 0 BEGIN
IF ( @objid > 0 ) AND ( @indid > 0 ) BEGIN
SELECT @strsql = 'update #splock2_table set objname = left(so.name,60), indname = left( si.name, 60 )  from #splock2_table t, '+@dbname+'.dbo.sysobjects so, '+@dbname+'.dbo.sysindexes si' 
SELECT @strsql = @strsql + ' where t.objid = so.id  and t.indid=si.indid and so.id = si.id and t.id = '+convert(varchar(20),@id)
EXEC(@strsql)
END ELSE
IF ( @objid > 0 ) BEGIN
SELECT @strsql = 'update #splock2_table set objname = left( so.name,60) from #splock2_table t, '+@dbname+'.dbo.sysobjects so where so.id  = t.objid and t.id = '+convert(varchar(20),@id)
EXEC(@strsql)
end
FETCH NEXT FROM @test INTO @id, @dbname, @objid, @indid
END
CLOSE @test
DEALLOCATE @test

SELECT spid,dbid,dbname,objid,objname,indid,indname,type,resource,mode,status 
FROM #splock2_table 
ORDER by spid


DROP TABLE #splock2_table
END
GO