Getting Table and Index names for fragmented indexes in SQL Server

To find table and index names for fragmented indexes with fragmentation larger than 20% and page count larger than 100 pages (run in the database for which you want to see the list of fragmented indexes):

SELECT
OBJECT_NAME(object_id) AS Table_Name
,(SELECT name FROM sys.indexes WHERE object_id = i.object_id and index_id = i.index_id) as Index_Name
,avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) i
WHERE avg_fragmentation_in_percent > 20 and page_count > 100
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY avg_fragmentation_in_percent DESC

For a more detailed result with database name and schema name for fragmented indexes with fragmentation larger than 20% and page count larger than 100 pages (run in the database for which you want to see the list of fragmented indexes):

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, ob.name AS Table_Name
, ind.name AS Index_Name
, pt.avg_fragmentation_in_percent
, pt.fragment_count
, pt.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'limited') pt
INNER JOIN sys.objects ob ON pt.object_id = ob.object_id
INNER JOIN sys.schemas sc ON ob.schema_id = sc.schema_id
INNER JOIN sys.indexes ind ON pt.object_id = ind.object_id AND pt.index_id = ind.index_id
WHERE pt.index_id > 0
AND pt.avg_fragmentation_in_percent > 20
AND pt.page_count > 100
ORDER BY avg_fragmentation_in_percent DESC

If you get error like this when running one of the codes:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

...you can replace DB_ID() with the database ID in the query that returns the error message.

To find out the database ID:

USE Database_Name
SELECT DB_ID() as DB_ID;
GO

To see how to list all indexes for all tables in a SQL Server database read related article:
List all indexes for all tables in a SQL Server database

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">