SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Database size info without cursors or temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stephe40
Posting Yak Master

218 Posts

Posted - 01/22/2004 :  17:56:27  Show Profile  Reply with Quote
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 int

select @low = low from
master.dbo.spt_values
where 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 unused
from 
	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

Edited by - stephe40 on 01/22/2004 18:03:52

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 01/23/2004 :  02:59:40  Show Profile  Visit Stoad's Homepage  Reply with Quote
Looks nice..

Plz, can you provide a strict definition of the table size?
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 01/23/2004 :  10:48:54  Show Profile  Reply with Quote
By table size I mean the size of the table within the database. The query will give you the reserved table size, how much of that is acutally being used by data and index pages, and how much is unused.

Its that what you were looking for?
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 01/23/2004 :  12:14:44  Show Profile  Visit Stoad's Homepage  Reply with Quote
Not quite so. Nevertheless that's ok. Thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000