Thursday, March 26, 2009

Quick SQL - getting the size of all tables in a database

I use this little chunk of SQL code almost every day. It gives you a relatively accurate report of the size of each table in the database, including indexes. Very handy!

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

No comments:

Post a Comment