Quick Check on Fragmentation

Just a quick check on sys.dm_db_index_physical_stats, returning the percentage of index fragmentation that is detected, for the given TABLENAME.

SELECT

    OBJECT_NAME(i.[object_id]) ObjectName,

    s.index_type_desc IndexType,

    i.name IndexName,

    s.partition_number [Partition#],

    ROUND(s.avg_fragmentation_in_percent, 2) [Fragmentation%]

FROM

    sys.dm_db_index_physical_stats

        (

        DB_ID(),

        OBJECT_ID('dbo.TABLENAME', 'U'), --<< CHANGE TO YOUR TABLE

        NULL,

        NULL,

        NULL

        ) AS s

    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

WHERE

    s.index_id = i.index_id;