March 18, 2014

Size of Individual Tables in Sql Server

I recently created a sql server database which was huge and so I wanted to find out individual sizes of the tables. So, with the help of few suggestions on the internet I ended up writing a script that displays the size of individual tables in KB, MB and the number of rows those tables have.

Here’s the script that I wrote.

  1. SELECT
  2.     t.NAME AS TableName,
  3.     s.Name AS SchemaName,
  4.     p.rows AS RowCounts,
  5.     SUM(a.total_pages) * 8 AS TotalSpaceKB,
  6.     SUM(a.used_pages) * 8 AS UsedSpaceKB,
  7.     SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
  8.     SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB
  9. FROM
  10.     sys.tables t
  11.       INNER JOIN
  12.     sys.indexes i ON t.OBJECT_ID = i.object_id
  13. INNER JOIN
  14.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  15. INNER JOIN
  16.     sys.allocation_units a ON p.partition_id = a.container_id
  17. LEFT OUTER JOIN
  18.     sys.schemas s ON t.schema_id = s.schema_id
  19. WHERE
  20.     t.NAME NOT LIKE 'dt%'
  21.     AND t.is_ms_shipped = 0
  22.     AND i.OBJECT_ID > 255
  23. GROUP BY
  24.     t.Name, s.Name, p.Rows
  25. ORDER BY
  26.     t.Name

 

And here is a screen shot of the results produced when run on Adventure Works database.

image

0 comments:

Reference: Shahed Kazi at AspNetify.com