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

use MyDB
go

DECLARE @names TABLE
(
  id   INT IDENTITY(1,1),
  name NVARCHAR(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @i INT = 1
DECLARE @str nvarchar(100)

INSERT INTO @names(name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT

DECLARE @space TABLE
(
  name     NVARCHAR(100) NULL,
  rows     CHAR(11),
  reserved NVARCHAR (15),
  data     NVARCHAR (18),
  indexes  NVARCHAR (18),
  unused   NVARCHAR (18)
)
WHILE @i <= @ROWCOUNT
BEGIN
  SELECT @str = name FROM @names WHERE id = @i
  INSERT INTO @space
  EXEC   sp_spaceused @str
  SET    @i += 1
END
SELECT * FROM @space
ORDER  BY CONVERT( BIGINT, rows ) DESC
;

SELECT			DB_NAME() AS DatabaseName
			, object_name(i.object_id) AS TableName
			, ISNULL(i.name, 'HEAP') AS IndexName
			, i.index_id AS IndexID
			, i.type_desc AS IndexType
			, p.partition_number AS PartitionNo
			, p.[rows] AS NumRows
			, au.type_desc AS InType
			, au.total_pages AS NumPages
			, au.total_pages * 8 AS TotKBs
			, au.used_pages * 8 AS UsedKBs
			, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p 
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE 
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id