Get size of all tables in database

Question

I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.

Is there an easy way to determine how much space on disk each table is consuming?

Answer 1

If you are using SQL Server Management Studio (SSMS), instead of running a query (which in my case returned duplicate rows) you can run a standard report. 

1. Right click on the database
2. Navigate to Reports > Standard Reports > Disk Usage By Table

Note: The database compatibility level must be set to 90 or above for this to work correctly. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx

Answer 2

SELECT      t.NAME AS TableName,     s.Name AS SchemaName,     p.rows,     SUM(a.total_pages) * 8 AS TotalSpaceKB,      CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,     SUM(a.used_pages) * 8 AS UsedSpaceKB,      CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM      sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN      sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN      sys.schemas s ON t.schema_id = s.schema_id WHERE      t.NAME NOT LIKE 'dt%'      AND t.is_ms_shipped = 0     AND i.OBJECT_ID > 255  GROUP BY      t.Name, s.Name, p.Rows ORDER BY      TotalSpaceMB DESC, t.Name