How to view table size for all tables in a database 1

[WalterHeisenberg]

Hello,

I'm using SQL server 2005 and would like to know how to view the table size for all tables in a given database.

I know I can use sp_spaceused <table> for a single table but I would like to view them all at once.

Is there another SP i should be using? I know I can use reporting services for this but I want to be able to write a simple query for it.

Help would be greatly appreciated. Thanks!

 

[gmmastros]

Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'''

Select * From #Temp

Drop Table #Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WalterHeisenberg]

Thank you George for the help but I am hoping you can help me just a bit more.

The code above results in a syntax error on the SP. I had tried something similar (well, spaceused for msforeachtable) but it bombed out with too many results). without success.

I know it is probably simple but I am so frustrated I can't even see straight! Thanks for your help in advance.

 

[gmmastros]

Sorry about that. When responding, I tested the code in Query Analyzer, but tested each part separately. To solve this, you should put EXEC before the sp_msforeachtable. Like this...

Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

[!]exec[/!] sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'''

Select * From #Temp

Drop Table #Temp

If this does not resolve your problem, then I suggest you post the actual code you are using and the error message you are getting. By providing more details, it's easier for us to help you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WalterHeisenberg]

Exec in front of a SP? Say it ain't so?

Boy, do I feel dumb. Everything worked like a charm - thank you!

 

[ESquared]

You should add a parameter to make sure the values you get are correct:

[tt]exec sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'', ''true'''[/tt]