Technical Article

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, and you can order it by whichever column you want.

Enjoy!

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

Rate

4.29 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

4.29 (7)

You rated this post out of 5. Change rating