Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 227

,func.avg_fragmentation_in_percent
,func.index_type_desc as IndexType
,func.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'ordDemo'),
NULL, NULL, NULL) AS func
JOIN
sys.indexes AS sysIn
ON
func.object_id = sysIn.object_id AND func.index_id = sysIn.index_
id
--Clustered Index's Index_id MUST be 1
--nonclustered Index should have Index_id>1
--with following WHERE clause, we are eliminating HEAP tables
--uncomment following line if you don't want to see
--fragmentation of HEAP
--WHERE sysIn.index_id>0;
--for gathering information of all indexes available in
--database This query may take long time to execute
SELECT
sysin.name as IndexName
,sysIn.index_id
,func.avg_fragmentation_in_percent
,func.index_type_desc as IndexType
,func.page_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
AS func
JOIN
sys.indexes AS sysIn
ON
func.object_id = sysIn.object_id AND func.index_id = sysIn.index_
id
WHERE sysIn.index_id>0;
How it works...
Pass your database ID with the DB_ID() function in sys.dm_db_index_physical_stats ,
along with the object ID (in our case, OBJECT_ID(N'ordDemo') ) of the table name for which
fragmentation information is required. This system function provides very detailed information
regarding index. It will not have index name, but it displays index ID. For getting the exact
name of the index, one has to make a join with the sys.Indexes system catalog.