x

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?

thanks!
more ▼

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

clayp56 gravatar image

clayp56
71 9 9 10

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

3 answers: sort newest

Yes, there is a dmv. You can use dm_db_partition_stats ([details][1]) 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]
[1]: http://msdn.microsoft.com/en-us/library/ms187737.aspx
more ▼

answered May 01, 2012 at 08:10 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

JohnM gravatar image

JohnM
6.9k 1 3 7

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

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

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

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
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x41

asked: Apr 30, 2012 at 02:39 PM

Seen: 17085 times

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