select object_name(A.object_id), I.Name, A.avg_fragmentation_in_percent,*
from sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'detailed') as A -- detailed shows the record_count
inner join sys.indexes as I
On A.object_id = I.object_id And A.index_id = I.index_id
avg_fragmentation_in_percent:
<=30 - Reorganize
>30 - Rebuild
