Easily calculate the size of your tables

I use this chunk of code at least once a week. It gives you a quick and easy reference on how big your tables are. This is about as accurate as the system stored procedure sp_spaceused (since it uses the same base tables), but it’s handier, since it runs on all the tables in a database.

select
TableName = convert(varchar(100),sysobjects.name)
,TotalRows = max(sysindexes.rows)
,MbData = floor(sum(convert(real,sysindexes.dpages)) * spt_values.low / 1048576)
,MbTotal = floor(sum(convert(real,sysindexes.used)) * spt_values.low / 1048576 )
from sysobjects
join sysindexes
on sysobjects.id = sysindexes.id
join master.dbo.spt_values spt_values
    on spt_values.number = 1
    and spt_values.type = ‘E’
where
sysobjects.type = ‘U’
and indid in (0,1,255)
group by
sysobjects.name
,spt_values.low
order by 4 desc

A nice and simple work By Sylvia Moestl Vasilik

Advertisements

~ by UTS on May 4, 2009.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: