<< SQL Server: Retrieving the number of rows in all tables in a database | Home | .NET: Multithreaded Platform Invoke and Unmanaged Thread-Local Storage >>
SQL Server: Which tables are storing the most physical data?

Overview

Following on from my previous post, here's a quick way to find out which data tables are using the most disk space...

References

Solution

SELECT o.name AS table_name, SUM(au.data_pages) as pages,
       ((SUM(au.data_pages) * 8192) / (1024 * 1024)) AS MB
FROM sys.allocation_units AS au
     JOIN sys.partitions AS p ON au.container_id = p.partition_id
     JOIN sys.tables AS o ON p.object_id = o.object_id
     JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
GROUP BY o.name
ORDER BY pages desc, o.name
GO

Versions

Metadata


Bookmark with :
Digg It! DZone StumbleUpon Technorati Reddit Del.icio.us Newsvine Furl Blinklist
posted @ Wednesday, January 09, 2008 3:16 PM | in SQL Server Software Development IT Management

Comments

Gravatar
# re: SQL Server: Which tables are storing the most physical data?
Posted by Rob von Nesselrode
on 4/5/2009 9:34 AM
Doesn't appear to give the correct result where there is a large amount of data in fields of type "image". We use these to store pdf's and the like and all the methods for determining size all seem to fail on this field.
Gravatar
# re: SQL Server: Which tables are storing the most physical data?
Posted by Stephen Horsfield
on 4/6/2009 8:11 AM
No, this method won't work on LOBs as these are not stored in the same way. You can determine the amount of data in LOB columns (image, text, ntext and the 2005+ var...(max) types) in other ways, such as using the DATALENGTH function
Gravatar
# re: SQL Server: Which tables are storing the most physical data?
Posted by Stephen Horsfield
on 4/6/2009 8:16 AM
See also the system view sys.tables and the column "lob_data_space_id" in SQL Server 2005+. Sorry I can't give a fully worked example at the moment.

Post Comment

Title *
Name *
Email
Url
Comment *  


Please add 4 and 2 and type the answer here: