/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  OBJECT_SCHEMA_NAME(i.[object_id]) + '.' + OBJECT_NAME(i.[object_id]) AS TableName,
  ISNULL(REPLACE(i.name, NCHAR(31), ''), '<<<HEAP>>>') AS IndexName,
  i.is_primary_key AS PK,
  i.is_unique_constraint AS UQ,
  CAST(CASE WHEN i.index_id = 1 THEN 1 ELSE 0 END AS BIT) AS Clust,
  CAST(p2.SizeMB AS DECIMAL(20, 2)) AS SizeMB,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_seeks + s.user_scans + s.user_lookups AS total_user_reads,
  CAST(s.user_scans*SizeMB/1024. AS DECIMAL(30, 2)) AS TotalScanGB,
  CASE WHEN i.index_id IN (0,1) THEN mi.MissingIndexes ELSE NULL END AS MissingIndexes
FROM sys.indexes AS i
LEFT JOIN
    (
      select p2.object_id, p2.index_id, SUM(au.used_pages) / 128.AS SizeMB
      from sys.partitions AS p2
        INNER JOIN sys.allocation_units AS au ON p2.partition_id = au.container_id
      where au.type <> 2-- LOB_DATA
      group by p2.object_id, p2.index_id
    ) p2 ON p2.object_id = i.object_id AND p2.index_id = i.index_id
LEFT JOIN 
  (
    SELECT mid.object_id, MissingIndexes = COUNT(*)
    FROM sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
    WHERE mid.database_id = DB_ID()
    GROUP BY mid.object_id
  ) mi ON mi.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
  AND s.database_id = DB_ID()
WHERE ((OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1) OR (OBJECTPROPERTY(i.[object_id],'IsView') = 1))
ORDER BY TotalScanGB DESC
OPTION(MAXDOP 1)