MSSQL - Find Which Table is Locked

exec sp_who2 active

set nocount on

DECLARE @SPID INT

declare @dbid char(20)

declare @objid int

declare @type char(8)

declare @mode char(8)

declare @status char(8)

declare @dosql varchar(8000)

              

DECLARE runme CURSOR  FOR

      select      distinct convert (smallint, req_spid) As spid,

            sd.name as db,

            rsc_objid As ObjId,

            substring (v.name, 1, 4) As Type,

            substring (u.name, 1, 8) As Mode,

            substring (x.name, 1, 5) As Status

      from master.dbo.syslockinfo lock (NOLOCK),

            master.dbo.spt_values v (NOLOCK),

            master.dbo.spt_values x (NOLOCK),

            master.dbo.spt_values u (NOLOCK),

            master..sysdatabases sd (NOLOCK)

      where   lock.rsc_type = v.number

                  and v.type = 'LR'

                  and lock.req_status = x.number

                  and x.type = 'LS'

                  and lock.req_mode + 1 = u.number

                  and u.type = 'L'

                  and sd.dbid = lock.rsc_dbid

      and rsc_objid <> 0

      and sd.name <> 'master'

      --and u.name = 'Sch-S   '

OPEN runme

FETCH NEXT FROM runme INTO @SPID,@dbid,@objid,@type,@mode,@status

WHILE @@FETCH_STATUS = 0

begin

select @SPID spid,@dbid db,@objid objid,@type type,@mode mode,@status status into ##tmp2

set @dosql = 'select LEFT(so.name,50),tb.* from ##tmp2 tb (NOLOCK) left join '+rtrim(@dbid)+'.dbo.sysobjects so (NOLOCK) on so.id = tb.objid'

--select @dosql

exec (@dosql)

drop table ##tmp2

FETCH NEXT FROM runme INTO @SPID,@dbid,@objid,@type,@mode,@status

end

CLOSE runme

DEALLOCATE runme