sp_helpdefrag reports defragmentation information for either all eligible objects for reorg defrag in the database whose context it is invoked from or for the given object if it is eligible for reorg defrag.

sp_helpdefrag uses the built-in defrag_status() on each of the required tables or on each of the required data partitions to get the information about defragmentation.
  • If table_name is not specified, defragmentation information for all eligible tables for reorg defrag (that is, user tables with datarows or datapages locking scheme) is reported. Rows for tables on which reorg defrag is currently executing precede those for tables where reorg defrag is not currently executing. Among these two sets, rows are in ascending order of the pct_defrag.
  • If table_name is specified, and if the table is eligible for reorg defrag, defragmentation information of the table as well as that of each data partition is reported. Rows are in the ascending order of percentage defragmented portion. Row for the table comes first and has NULL in partition column.
  • If partition_name is specified, only that particular data partition's information is reported.


The syntax is:
sp_helpdefrag [table_name][,partition_name]