SQL Server 2008 r2 table sizes

Need to look at all the tables and their sizes for a database. Is there a DMV that I can use to accomplish this task?


more ▼

asked Apr 30, 2012 at 02:39 PM in Default

avatar image

71 9 9 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Use reports.

Right click a database, choose reports, then 'Disk usage by Table'. This will give you a report that details for each table, the number of rows and how much disk space is taken up by the table (separated out as data + indexes).

If you need to add any more info, or tweak it slightly, capture the SQL that the report uses to generate the data, and build your own table reporting query.

more ▼

answered Apr 30, 2012 at 02:47 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

For some reason, I always forget about those reports!! ;-) Excellent suggestion. Nice & easy.

Apr 30, 2012 at 02:50 PM JohnM

John and Kev thanks so much for your help. By the way, Kev how do i capture the SQL for the report?

Apr 30, 2012 at 02:56 PM clayp56

Use Profiler or server-side trace. To be honest, it's going to give you something like @JohnM suggested anyway.

Apr 30, 2012 at 02:58 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

You can do something like this:

 --- SQL2005
 select o.name
 , reservedpages = sum(a.total_pages)
 , usedpages = sum(a.used_pages)
 , pages = sum(case when a.type <> 1 then a.used_pages
 when p.index_id < 2 then a.data_pages else 0 end)
 , SUM(a.used_pages)*8096 AS 'Size(B)'
 , rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
 from sys.objects o
 join sys.partitions p on p.object_id = o.object_id
 join sys.allocation_units a on p.partition_id = a.container_id
 where o.type = 'U'
 group by o.name
 order by 3 desc --biggest tables first

Modified to show the size of the used pages. Borrowed from here: http://www.dbforums.com/microsoft-sql-server/1003535-query-showing-tablesize-mb.html

This should work in SQL 2005 & SQL 2008.

Hope this helps!!

more ▼

answered Apr 30, 2012 at 02:49 PM

avatar image

14.5k 3 7 15

(comments are locked)
10|1200 characters needed characters left

Yes, there is a dmv. You can use dm_db_partition_stats (details) to get this information:

 SELECT    '[' + OBJECT_SCHEMA_NAME([ddps].[object_id]) + '].['
     + OBJECT_NAME([ddps].[object_id]) + ']' AS [table_name] ,
     SUM([ddps].[in_row_data_page_count]) AS  [in_row_data_page_count] ,
     SUM([ddps].[in_row_used_page_count])  AS [in_row_used_page_count] ,
     SUM([ddps].[in_row_reserved_page_count])  AS [in_row_reserved_page_count] ,
     SUM([ddps].[lob_used_page_count]) AS  [lob_used_page_count] ,
     SUM([ddps].[lob_reserved_page_count])  AS [lob_reserved_page_count] ,
     SUM([ddps].[row_overflow_used_page_count])  AS [row_overflow_used_page_count] ,
     SUM([ddps].[row_overflow_reserved_page_count])  AS [row_overflow_reserved_page_count] ,
     SUM([ddps].[used_page_count]) AS  [used_page_count] ,
     SUM([ddps].[reserved_page_count])  AS [reserved_page_count] ,
     SUM([ddps].[row_count]) AS  [row_count]
 FROM    [sys].[dm_db_partition_stats] AS ddps
     INNER JOIN [sys].[tables] AS t ON [ddps].[object_id] = [t].[object_id]
 GROUP BY [ddps].[object_id]

more ▼

answered May 01, 2012 at 08:10 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 30, 2012 at 02:39 PM

Seen: 22561 times

Last Updated: May 01, 2012 at 08:10 AM

Copyright 2018 Redgate Software. Privacy Policy