====== Summarise space usage ======
{{tag> space disk housekeeping}}

===== By table =====
<code tsql>SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME(p.[object_id])) + '.' + QUOTENAME(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
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</code>

==== sp_spaceused ====

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.

<file tsql 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
</file>

===== By database =====

<code tsql>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</code>