Finding Fragmented Indexes in a Database Running in SQL Server 2000 Compatibility Mode on 2005

The Index Physical Statistics report in SQL Management Studio is an easy way to identify fragmented indexes. But the report won’t run against a database set to 8.0 (SQL Server 2000) compatibility mode. Here’s a script that will generate a list of fragmented indexes for databases running with 8.0 mode:

USE Your Database;      — change the name of the target database here and in the variable @dbname below





DECLARE @frag float;

DECLARE @dbname nvarchar(130);

DECLARE @dbid int;


— Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

— and convert object and index IDs to names.

SET @dbname = N‘Your Database— change the name of the target database here

SET @frag = 10.0              — change this value to adjust the threshold for fragmentation


SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = @dbname



    PS.object_id AS Objectid, AS ObjectName, AS SchemaName, AS IndexName,

    PS.index_id AS IndexId,

    PS.partition_number AS PartitionNum,

    ROUND(PS.avg_fragmentation_in_percent, 2) AS Fragmentation,

      PS.record_count AS RecordCount

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ‘SAMPLED’) PS

      JOIN sys.objects O ON PS.object_id = O.object_id

      JOIN sys.schemas S ON S.schema_id = O.schema_id

      JOIN sys.indexes I ON I.object_id = PS.object_id

            AND I.index_id = PS.index_id

WHERE PS.avg_fragmentation_in_percent > @frag AND PS.index_id > 0

ORDER BY record_count desc;

This script was adapted from a script originally published on MSDN under the SQL Server Books Online topic sys.dm_db_index_physical_stats.