Attached to this KB article is an SQL query that assesses the level of index fragmentation within an SQL Server 2005 database (and later).
This query shows the size of the index (in 8KB pages), an estimated amount of fragmentation (as a percentage), and the product of these two values. If the product is greater than 5000 the index is displayed. Generally, indexes with values greater than about 10,000 are considered too fragmented.
This query lists larger tables with moderate or high fragmentation percentages, and only lists smaller table with high or very high fragmentation percentages.
If indexes are too fragmented, then rebuilding indexes more often may significantly improve performance. See for example:
Creating a maintenance plan in SQL Server 2005 or 2008 to optimize database performance
Note, this query is based on estimated fragmentation level, and highlights indexes based on a simple heuristic. As such, while the results may help identify cases of extreme fragmentation, it may be less helpful in case of moderate to low fragmentation.