DECLARE
@DBID INT
SELECT
@DBID = DB_ID()
Identifying the High / Low Fragmentation of Objects in the 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