SELECT SO.NAME AS TableName, PS.object_id AS ObjectId, SI.NAME AS IndexName, PS.index_id AS IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage,
SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS ROW_COUNT,
MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent',
MAX(fragment_count) AS FragmentCount,
MAX(ips.page_count) AS PageCount,
MAX(ius.user_scans) AS UserScans, MAX(ius.user_seeks) AS UserSeeks, MAX(ius.user_lookups) AS UserLookups
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') AS ips
ON ps.object_id = ips.object_id AND
       ps.index_id = ips.index_id
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id
WHERE
SO.IS_MS_SHIPPED = 0
GROUP BY so.name, ps.object_id, si.name, ps.index_id
ORDER BY UserLookups DESC

SELECT SO.NAME AS TableName, PS.object_id as ObjectId, SI.NAME AS IndexName, PS.index_id as IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage, SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS Row_Count, MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent', MAX(fragment_count) as FragmentCount, MAX(ips.page_count) as PageCount, MAX(ius.user_scans) as UserScans, MAX(ius.user_seeks) as UserSeeks, MAX(ius.user_lookups) as UserLookups FROM SYS.DM_DB_PARTITION_STATS PS inner join sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') as ips on ps.object_id = ips.object_id and ps.index_id = ips.index_id INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID AND SI.INDEX_ID = PS.INDEX_ID LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id WHERE SO.IS_MS_SHIPPED = 0 group by so.name, ps.object_id, si.name, ps.index_id order by UserLookups desc