Table Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Table Size

Author

Topic

vinayj
Starting Member

32 Posts

Posted - 2005-06-10 : 11:51:49

I have a huge database with lots of tables in them. Only some of the tables are highly populated. I want to know the size of only those tables.

Vinay

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-10 : 11:55:26

exec sp_spaceused 'tablename'

Go with the flow & have fun! Else fight the flow

andylow
Starting Member

1 Post

Posted - 2005-06-22 : 06:28:34

or for the whole DB full
EXEC sp_MSforeachtable @command1="print '?' exec sp_spaceused '?'"

Kristen
Test

22859 Posts

Posted - 2005-06-22 : 07:18:49

http://www.databasejournal.com/scripts/print.php/1450801

Might need a DBCC UPDATEUSAGE (0) first though ...

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-22 : 07:33:37

And if you hate cursors (like I do) you can use this:

select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY reserved desc

And yeah, DBCC UPDATEUSAGE is important if you want the most accurate results.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-22 : 14:18:24

quote:
Originally posted by robvolk

And if you hate cursors (like I do) you can use this:

select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY reserved desc

And yeah, DBCC UPDATEUSAGE is important if you want the most accurate results.

Just FYI - the algorithmic holes that could cause the page/row counts to get out of whack have all been fixed in SQL Server 2005. Any databases created on it (or upgraded and then DBCC UPDATEUSAGE run once) will not have any further problems in this area.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Kristen
Test

22859 Posts

Posted - 2005-06-22 : 14:36:19

That's great to hear Paul. (I kinda feel that SQL2005 should either run the DBCC as part of the upgrade, or somehow alert that it would be beneficial to be run - dunno if that's a posibility though?)

Good to have your input here, perhaps we'll even let Tara take a holiday!

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-22 : 16:16:56

quote:
Originally posted by Kristen

That's great to hear Paul. (I kinda feel that SQL2005 should either run the DBCC as part of the upgrade, or somehow alert that it would be beneficial to be run - dunno if that's a posibility though?)

Good to have your input here, perhaps we'll even let Tara take a holiday!

Kristen

It's going to be pointed out and documented. We can't do it automatically though as it can take a long time to run, depending on db size.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources