I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator. /* Original by: Bill Graziano (SQLTeam.com)Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40)*/declare @low intselect @low = low frommaster.dbo.spt_valueswhere number = 1 and type = 'E'select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved, (d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp, ((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unusedfrom sysobjects o inner join (select distinct id, rowcnt from sysindexes where keys is not null and first != 0) ro on o.id = ro.id inner join (select id, sum(reserved) reserved from sysindexes where indid in (0, 1, 255) group by id) r on o.id = r.id inner join (select c.id, dpages+isnull(used, 0) data from (select id, sum(dpages) dpages from sysindexes where indid < 2 group by id) c full outer join (select id, isnull(sum(used), 0) used from sysindexes where indid = 255 group by id) t on c.id = t.id) d on r.id = d.id inner join (select id, sum(used) used from sysindexes where indid in (0, 1, 255) group by id) i on d.id = i.id where o.xtype = 'U' order by reserved desc
Database size info without cursors or temp tables
Posted - 2004-01-22 : 17:56:27
