----Unused tables & indexes.
----Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index
Declare @dbid int
Select @dbid = db_id(‘AdventureWorks')
Select objectname=object_name(i.object_id)
, indexname=i.name, i.index_id
from sys.indexes i, sys.objects o
where objectproperty(o.object_id,'IsUserTable') = 1
and i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = @dbid )
and o.object_id = i.object_id
order by objectname,i.index_id,indexname asc