---

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id, DB_ID()) AS [Schema],
    OBJECT_NAME(ps.object_id, DB_ID()) AS [Table],
    i.name AS [Index],
    ps.index_type_desc,
    ps.page_count,
    ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) ps
JOIN sys.indexes i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
WHERE ps.index_id > 0
  AND ps.page_count > 1000
ORDER BY ps.avg_fragmentation_in_percent DESC;