T-SQL to find Fragmented Indexes

Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.
Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.

--Replace this with the name of the Database for which we want to find the fragmentation.

USE <DBName> 

GO

DECLARE @DBName AS VARCHAR(10) = 'DBName'

DECLARE @DBID AS INT = DB_ID(@DBName)

DECLARE @AllowedFragmentation AS INT = 70 --A acceptable value in Percent(%) for fragmentation.

DECLARE @Qry AS VARCHAR(MAX)

SELECT

--@DBID [DBID],

--@DBName DBName,

PS.OBJECT_ID ObjectID,

COALESCE(T.name,V.name) ObjectName,

PS.index_id,

I.name IndexName,

PS.page_count AS TotalPages,

(PS.page_count * 8)/1024.0 as TotalMB,

((PS.page_count * 8)/1024.0) * (PS.avg_fragmentation_in_percent/100) as ReclaimableMB,

PS.avg_fragmentation_in_percent AvgFragmentationPercent

FROM

sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes AS I

ON PS.OBJECT_ID = I.OBJECT_ID

AND PS.index_id = I.index_id  

LEFT JOIN sys.tables T

ON T.object_id = I.object_id

LEFT JOIN sys.views V

ON V.object_id = I.object_id

WHERE

PS.database_id = @DBID

AND PS.avg_fragmentation_in_percent > @AllowedFragmentation 

ORDER BY    

PS.avg_fragmentation_in_percent DESC 

Here, I have considered 70% fragmentation as an acceptable level of fragmentation.

Hope, this helps.