Sizes of all indexes

AllIndexSizes.sql

-- get the row count and space used stats for each index
-- in this database

SELECT
	s.Name as SchemaName, t.Name as TableName, i.name as IndexName,
	p.Rows as RowCounts, SUM(AU.Total_pages) * 8.0/1024 AS TotalSpaceMB,
	SUM(AU.used_pages) * 8.0/1024 AS UsedSpaceMB,
	(SUM(AU.Total_Pages) - SUM(AU.used_pages)) * 8.0/1024 AS UnusedSpaceMB
  FROM sys.tables AS T
  JOIN sys.indexes AS I ON T.object_id = I.object_id
  JOIN sys.partitions AS P on I.object_id = P.object_id AND I.index_id = P.index_ID
  JOIN sys.allocation_units AS AU on P.partition_id = AU.container_id
  LEFT OUTER JOIN sys.schemas AS S on T.Schema_ID = S.Schema_ID
  WHERE T.is_ms_shipped = 0
  GROUP BY T.Name, S.Name, I.Name, P.Rows
  ORDER BY s.Name, t.Name;