how to find out defragment index pages in sql serv

Question

Hi i am using SQL SERVER 2008 R2 and i wanted to know how to get the defragment index pages in size for particular object, index in sql server 2008 r2 using script. can some one please help me to get this. thanks! in adavanced.

Answer 1

Use this query:

SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),  OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL)