Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc