SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME ,
SI.NAME AS INDEX_NAME ,
avg_fragmentation_in_percent AS FRAGMENT_PERCENT ,
DMV.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
AS DMV
LEFT OUTER JOIN SYS.INDEXES AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID
AND DMV.INDEX_ID = SI.INDEX_ID
--WHERE avg_fragmentation_in_percent > 10
AND index_type_desc IN ( 'CLUSTERED INDEX', 'NONCLUSTERED INDEX' )
AND DMV.record_count >= 2000
--ORDER BY TABLE_NAME DESC
ORDER BY FRAGMENT_PERCENT desc

We can also check the individual index fragmentation of an index by using SSMS.

To determine index fragmentation in SQL Server, you can use the following steps:

1. Connect to the SQL Server instance using a tool like SQL Server Management Studio (SSMS)
2. Navigate to the index you want to check
3. Right click and goto properties
4. Select Fragmentation, it will show the fragmentation in %