select a.*,b.AverageFragmentation from 
(
	SELECT
	tbl.name AS [Table_Name],
	tbl.object_id,
	i.name AS [Name],
	i.index_id,
	CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
	CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],
	CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]
	FROM
	sys.tables AS tbl
	INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
)a
inner join
(
	SELECT
	tbl.object_id,
	i.index_id,
	fi.avg_fragmentation_in_percent AS [AverageFragmentation]
	FROM
	sys.tables AS tbl
	INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
	INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
)b
on a.object_id=b.object_id and a.index_id=b.index_id

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass