Check Fragmentation on Objects in SQL Database

This article was posted more than 1 year ago. Please keep in mind that the information on this page may be outdated, insecure, or just plain wrong today.

tsql so I can remember in the future.

declare @db_id int
declare @object_id int
set @db_id = DB_ID('EMR')
set @object_id = 0
select  db_name(@db_id) as [database], object_id, object_name(object_id, @db_id) as object_name,
index_id,
avg_fragmentation_in_percent,
page_count
from sys.dm_db_index_physical_stats(@db_id, null, null, null, 'LIMITED')
where page_count > 1000
and
avg_fragmentation_in_percent > 35
order by avg_fragmentation_in_percent desc
database                                                                                                                         object_id   object_name                                                                                                                      index_id    avg_fragmentation_in_percent page_count
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------- --------------------
EMR                                                                                                                              1091587027  ARCH_HXDIAGNOSIS                                                                                                                 1           35.2092352092352             1386