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

GO

 

SET NOCOUNT ON;

 

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

 

SELECT

    PS.object_id AS Objectid,

      O.name AS ObjectName,

      S.name AS SchemaName,

      I.name 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.