One of the items in my toolbox is this simple query that returns the number of pages used by different tables in the database. The nice thing is that it gives one simple number that includes indexes, lob data – basically the whole table.
SELECT TOP 20 s.name, o.name, SUM(ddps.used_page_count) FROM sys.dm_db_partition_stats AS ddps INNER JOIN sys.objects AS o ON ddps.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id GROUP BY s.name, o.name ORDER BY SUM(ddps.used_page_count) DESC;
I just used this on a database today to find that there are some leftover tables from a migration a while back that are eating up a considerable amount of space. Time to run some DROP TABLE scripts!