Find the Index fragmentation levels and Recomendations

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
           'Time to Defrag'
           WHEN avg_fragmentation_in_percent > 30 THEN 'Time to Reindex'
           ELSE 'No Action Required Now'
         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' )
       AND IPS.indes_level = 0 -- leaf level only
ORDER  BY recomendation DESC