Identifying Fragmentation of Tables

DECLARE @DBID INT
SELECT @DBID = DB_ID()

--- Identifying the High / Low Fragmentation of Table(s) in the active Database
SELECT OBJECT_NAME(FRG.[OBJECT_ID]) 'TABLE NAME',INDEX_TYPE_DESC 'INDEX TYPE',IND.[NAME],CASE WHEN FRG.AVG_FRAGMENTATION_IN_PERCENT <30 THEN 'To Be Re-Organized' ELSE 'To Be Rebuilt' END 'ACTION TO BE TAKEN' ,FRG.AVG_FRAGMENTATION_IN_PERCENT '% FRAGMENTED'
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(@DBID, NULL, NULL, NULL, NULL) FRG JOIN SYS.SYSINDEXES IND
ON (IND.ID =FRG.[OBJECT_ID] AND IND.INDID = FRG.INDEX_ID)
WHERE FRG.AVG_FRAGMENTATION_IN_PERCENT > 0
AND FRG.DATABASE_ID = @DBID
AND IND.FIRST IS NOT NULL
AND IND.[NAME] IS NOT NULL
ORDER BY 5 DESC

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions