detect index fragmentation

Detecting index fragmentation for sql 2005 or above :

SELECT
                    DB_NAME() as database_name,
                    (SELECT DISTINCT ss.name+'.'+so.name FROM sys.objects

so
                        INNER JOIN sys.indexes on so.object_id=si.object_id
                        INNER JOIN sys.schemas AS ss on ss.schema_id = SO.schema_id
                    ) parenttable ,
                    si.name as indexname,
                    ps.avg_fragmentation_in_percent as frag,
                    i.rows,
                    (i.rows*ps.avg_fragmentation_in_percent) as tot_frg
            FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
            INNER JOIN sys.indexes AS si ON ps.OBJECT_ID = si.OBJECT_ID AND ps.index_id

=si.index_id
            INNER JOIN sysindexes i ON ( si.OBJECT_ID = i.id  and i.rows > 1000 )
            WHERE ps.database_id = DB_ID() and si.name is not null
            AND ps.index_id >0
            AND si.name=i.name

Detecting index fragmentation for sql 2000 or below :

 -- change the name of the target database here and in the variable @dbname below
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'use your db name'-- change the name of the target database here
SET @frag = 1.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;