Got this question from a newsgroup today. The answer is pretty simple, just use the dynamic management view sys.dm_db_index_physical_stats. I'm posting this here mostly so I have somewhere to refer to when asked this question...
I prefer to have a helper function to get the index name:
CREATE FUNCTION dbo.index_name (@object_id int, @index_id int)
RETURNS sysname
AS
BEGIN
RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id)
END;
GO
And then a simple query:
SELECT
OBJECT_NAME(object_id) AS tblName
,dbo.index_name(object_id, index_id) AS ixName
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 20
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
Then you just adjust the search clause to your liking. One hint is to exclude nindexes with few pages (the page_count column).