View SQL 2005 Table Fragmentation

To view how fragmented your table is, run the following query:

USE dbname
GO
SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
INDEX_ID,
CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('dbname'),OBJECT_ID('dbo.tablename'),NULL,NULL,NULL );
GO