What Table’s Index is Being Rebuilt

by bitznbitez

In rebuilding a bunch of indexes overnight I began to wonder where in the process SQLServer was.   Due to locking the use of object_name(id) was not working.   So I combined uncommitted read with a join to sys.tables like so…

set transaction isolation level read uncommitted;
select name from sys.tables where object_id in (
  select distinct resource_associated_entity_id
  from
    sys.dm_tran_locks
  where
             resource_type = 'OBJECT'
    and resource_database_id = db_id()
    and request_mode = 'Sch-M'
  )
;

The query worked like a champ.

Cheers!

Advertisements