Pages Used

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!