Check index fragmentation on every table in sql server

Hi Today, I would like to share you one simple query that how to check fragmentation details on one database on every table. Using below query we can get fragmentation details on every table and every index.

As per  below query, This will shows results as table name, Index name, Index type, number of pages in count and fragmentation details of each index on a particular table of Database.

USE dbnamehere
go

SELECT
OBJECT_NAME(i.object_id) AS TableName ,
i.name AS IndexName ,
index_type_desc,
page_count,
phystat.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID('dbnamehere'), NULL, NULL, NULL,'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 10

Note: As per Microsoft, If  index fragmentation rate between 5-30% we can go with Index Re-Organize operation. If the fragmentation cross 30% than we should Rebuild with Rebuild index operation.