Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find the largest tables in a database

From time to time, I need to find the largest tables in terms of rows / used space / total space etc. I originally used sp_spaceused, a temp table and a cursor (or sp_MSForEachTable) and put them into a stored procedure to get the information I need.

But now as I am working more on table partition management, I find there is a DMV that can give me the exactly info I need, and what surprises me is that sp_spaceused is actually using this DMV, i.e. sys.dm_db_partition_stats to get the data as well. So here is the one sql to get the largest tables in a db (only applicable to sql server 2k5 / 2k8 for tables without xml indexes)

-- find the table size info (no xml index) using sys.dm_db_partition_stats
-- Author: Jeffrey Yao
-- Date: Sept 27, 2010

select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*sum( case
     when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
     else lob_used_page_count + row_overflow_used_page_count
    end )
, index_kb=8*(sum(used_page_count)
    - sum( case
           when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
        else lob_used_page_count + row_overflow_used_page_count
        end )
     )    
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by
rows desc
-- data_kb desc
-- reserved_kb desc
-- data_kb desc
-- index_kb desc
-- unsed_kb desc

Comments

Posted by vin on 29 September 2010

Running this SQL returns an error:

Msg 195, Level 15, State 10, Line 1

'object_schema_name' is not a recognized function name.

Posted by Jeffrey Yao on 29 September 2010

Vin, are your running in sql server 2000? object_schema_name is introduced in sql 2k5 / 2k8, but not in sql server 2000, AFAIK.

Posted by SRINATH-249749 on 4 October 2010

Jeffrey,

We can also generate this report in sql2008 (with out any script). by right click on DB and Click on "Report" and select "Standard Reports" and Click on "Disk Usage by Top tables".

Thanks,

Srinath

Posted by todd on 4 October 2010

Maybe I am missing the point, but if sp_spacedused uses the same Dynamic Management View, then why not use the following? The results are exactly the same.

CREATE TABLE #TableSizes

(

           TableName NVARCHAR(255),

           TableRows INT,

           ReservedSpaceKB VARCHAR(20),

           DataSpaceKB VARCHAR(20),

           IndexSizeKB VARCHAR(20),

           UnusedSpaceKB VARCHAR(20)

)

INSERT INTO #TableSizes

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT * FROM #TableSizes

ORDER BY TableRows DESC

DROP TABLE #TableSizes

Posted by skamath on 4 October 2010

Jeffrey Yao,

OBJECT_SCHEMA_NAME was indeed introduced in SQL Server 2005, but only with SP2

Posted by thermo_ll on 16 May 2012

@SRINATH-249749

Standard Report giving me an Error msg

Index(Zero Base) must be greater than or equal to zero and less than the size of argument list

Posted by milind_vakil on 19 June 2013

SRINATH-249749

Worked perfectly for me. Thanks a lot!

Posted by adi - doccolabs.com on 17 September 2014

This is an old thread, but there's an app called "Database File Explorer" which tells just that, and much more.

Leave a Comment

Please register or log in to leave a comment.