Identifying Fragmented Indexes

Code to identify all fragmented indexes in a SQL Server instance:


/****** Object:  StoredProcedure [dbo].[usp_getFRAG]    Script Date: 09/09/2011 05:22:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Author: Eugene Teh
Last Update: 9th Sept 2011
Name: usp_getFRAG
*/
CREATE PROCEDURE [dbo].[usp_getFRAG]
@fragPercent int, --Percentage of fragmentation
@pageCount int  --Number of index pages
AS
SELECT
FRAG.[database_id],
    OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
    SIX.[Name],
    FRAG.Avg_fragmentation_in_percent,
    FRAG.Page_count
FROM
    sys.dm_db_index_physical_stats
    (
        NULL,    --Returns stats for all DB in instance
        0,          --Parameter for object_id.
        DEFAULT,    --Parameter for index_id.
        0,          --Parameter for partition_number.
        DEFAULT     --Scanning mode. Default to "LIMITED"
    ) FRAG
    JOIN
    sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
FRAG.avg_fragmentation_in_percent > @fragPercent AND
FRAG.page_count > @pageCount
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC;