-- all fragmented indexes on current db, % fragmentation > 30
SELECT a.index_id, OBJECT_NAME(a.object_id), name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), 
      NULL, NULL, NULL, NULL) AS a  
     JOIN sys.indexes AS b 
     ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc
GO