Summarise space usage

WITH files AS (
  SELECT
    database_id
  , CAST(size*8 AS FLOAT)/1024 AS size  -- size in MB
  , type_desc
  FROM sys.master_files
), dbs AS (
  SELECT
    database_id
  , ROUND(SUM(size),1) AS TotalMB
  , ROUND(SUM(CASE type_desc WHEN 'ROWS' THEN size ELSE 0 END),1) AS DataMB
  , ROUND(SUM(CASE type_desc WHEN 'LOG' THEN size ELSE 0 END),1) AS LogsMB
  , ROUND(SUM(CASE WHEN type_desc NOT IN ('ROWS', 'LOG') THEN size ELSE 0 END),1) AS OtherMB
  FROM files
  GROUP BY database_id
)
-- SELECT SUM(TotalMB) FROM dbs
SELECT
  db.name AS DbName
, db.recovery_model_desc
, db.is_auto_shrink_on
, dbs.*
FROM sys.databases db
  INNER JOIN dbs ON dbs.database_id = db.database_id
ORDER BY TotalMB DESC
SELECT
  OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) AS TableName
, SUM(IIF(a.[type] = 1, p.[rows], 0)) AS [TableRows]
, SUM(a.used_pages) AS UsedPages
, IIF(SUM(a.used_pages) = 0, SUM(IIF(a.[type] = 1, p.[rows], 0)),
      SUM(IIF(a.[type] = 1, p.[rows], 0)) / SUM(a.used_pages)) AS RowsPerPage
, SUM(a.total_pages) AS TotalPages
, SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB
, MAX(p.[data_compression_desc]) AS CompressionType
FROM sys.partitions p
  INNER JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
  INNER JOIN sys.indexes i
    ON i.[object_id] = p.[object_id]
    AND i.index_id = p.index_id
WHERE p.index_id <= 1
-- 0 = heap, 1 = clustered -- table will have index with index_id of 0 *or* 1, not both
GROUP BY p.[object_id]
ORDER BY TotalSpaceMB DESC

This script calls sp_spaceused for every table or indexed view in the database. I don't like it much but that's just my preference.

spaceused.sql
-- declare table variable to hold list of tables & indexed views
DECLARE @tables TABLE (  
  SchemaName VARCHAR(128)
, TableName VARCHAR(128)
, ObjectId INT
, Reported BIT DEFAULT 0
)
 
-- collect list of tables & indexed views
INSERT INTO @tables (
  SchemaName
, TableName 
, ObjectId
)
SELECT DISTINCT
  s.name
, o.name
, o.object_id
FROM sys.schemas s
  INNER JOIN sys.objects o ON o.schema_id = s.schema_id
  LEFT JOIN sys.indexes i ON i.object_id = o.object_id
WHERE o.type = 'U'
OR (o.type = 'V' AND i.index_id IS NOT NULL)
 
-- declare table variable to hold sp_spaceused results
DECLARE @objId INT
DECLARE @sql VARCHAR(MAX)
 
DECLARE @results TABLE (
  SchemaName VARCHAR(128)
, TableName VARCHAR(128)  
, TableRows CHAR(11)
, DataSize VARCHAR(18)  
, IndexSize VARCHAR(18)  
, ReservedSpace VARCHAR(18)  
, UnusedSpace VARCHAR(18)  
) 
 
-- call sp_spaceused for each table in @tables and cache results
WHILE 1 = 1
BEGIN 
 
  SET @objId = NULL
 
  SELECT TOP 1
    @objId = ObjectId
  FROM @tables
  WHERE Reported = 0
 
  IF @objId IS NULL
    BREAK
 
  SELECT 
    @sql = 'sp_spaceused @objname = ''[' + SchemaName + '].[' + TableName + ']'''
  FROM @tables
  WHERE ObjectId = @objId
 
  INSERT INTO @results (
    TableName
  , TableRows
  , ReservedSpace
  , DataSize
  , IndexSize
  , UnusedSpace
  )
  EXEC (@sql)
 
  UPDATE r
  SET r.SchemaName = t.SchemaName
  FROM @results r
    INNER JOIN @tables t ON t.ObjectId = @objId
  WHERE r.SchemaName IS NULL
 
  UPDATE @tables
  SET Reported = 1
  WHERE ObjectId = @objId
 
END
 
-- inspect cached results
SELECT 
  SchemaName
, TableName
, CAST(TableRows AS INT) AS TableRows
, ROUND(CAST(REPLACE(DataSize, 'KB', '') AS FLOAT)/1024, 1) AS DataMb
, ROUND(CAST(REPLACE(IndexSize, 'KB', '') AS FLOAT)/1024, 1) AS IndexMb
, ROUND(CAST(REPLACE(UnusedSpace, 'KB', '') AS FLOAT)/1024, 1) AS UnusedMb
, ROUND(CAST(REPLACE(ReservedSpace, 'KB', '') AS FLOAT)/1024, 1) AS ReservedMb
FROM @results
ORDER BY 4 DESC