Some times we need to rebuild the index of the tables in MS-Sql database .In those case we consider the which tables real need the rebuild of the index.As per my knowledge below query is help to know which tables need rebuild of the index .
Declare @dbid int
Select convert(decimal(5,2), avg_fragmentation_in_percent) as avg_fragmentation_percent,
object_name(d.object_id) as [table],
i.name as [name], d.index_type_desc,
alloc_unit_type_desc as [type],
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, ‘SAMPLED’) d
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id