.::: Query index fragmentation level SQL Server with detail, specify Table, Database :::.

1. index fragmentation level

SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       -- ips.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
                AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc

2. index fragmentation level with Specify Database & Table on sys.dm_db_index_physical_stats db_id & Table

select @@servername as ServerName,db_name() as DBName,
  object_name(object_id) as object_name,
  index_id,
  index_type_desc,
  index_depth,index_level,
  avg_fragmentation_in_percent,
  avg_fragment_size_in_pages,
  avg_page_space_used_in_percent,
  page_count,fragment_count,record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('Sales.SalesOrderDetailEnlarged'),null,null,'detailed')
-- where index_depth > 0 and index_level > 0
order by avg_fragmentation_in_percent desc

3. index fragmentation level Detail with Specify Database & Table  with Inner Join

SELECT @@servername as ServerName, db_name() as DBName, OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
      OBJECT_NAME(ips.object_id) AS object_name,
      i.name AS index_name,
      i.type_desc AS index_type,
      ips.avg_fragmentation_in_percent,
      ips.avg_page_space_used_in_percent,
      ips.avg_fragment_size_in_pages,
      ips.page_count,
      ips.record_count,
      ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetailEnlarged'), null, null, 'DETAILED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
  AND
  ips.index_id = i.index_id
ORDER BY page_count DESC;