Table Size issue

Question

Table Size issue

Carla Stewart

Old Hand

Points: 353
More actions
January 17, 2002 at 2:41 pm

#77993

In Enterprise Manager 7.0 I know that you can see the size of the table when clicked on the database.
Is there a way to get that value in Transact SQL. I need to write a query that displays all tables and their size.
Thanks ahead of time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The sp_spaceused system stored procedure should work. If you want hit all the user tables, you can use the sp_MSForEachTable stored procedure like so:

EXEC sp_msForEachTable @Command1 = 'EXEC sp_spaceused N''?'''

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

The sp_spaceused system stored proc will bring back everything you want and the sp_MSforeachtable will let you get all the tables in the current db at once. I'm sure that this is just one way of doing it.

create table #TableSpace

(

name varchar(64),

rows int,

reserved varchar(64),

data varchar(64),

index_size varchar(64),

unused varchar(64)

)

go

insert into #TableSpace exec sp_MSforeachTable @command1 = 'sp_spaceused "?"'

select * from #TableSpace

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Carla Stewart Old Hand Points: 353 More actions · Answer 3

First of all I would like to thank you for teaching me the the sp_msForEachTable stored procedure. I have other scripts that I have created a cursor on to do the exact same thing.

Second of all, I would like to know if the information that is returned by the following statement is correct.

EXEC sp_msForEachTable @Command1 = 'EXEC sp_spaceused N''?'''

I ran that and the 'data' column does not seem to match up to the information displayed in Enterprise Manager 7.0 when you click on the database and display the Tables and Indexes information. Which one should I trust? Most of the tables are off by 8k more. ( I have refreshed the Enterprise Manager) but one table is 8k less.

Thanks again for the knowledge of sp_msForEachTable and sp_spaceused.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

sp_foreach uses cursors. Cursors aren't necessarily bad, just inefficient. You can usually find other ways of doing things, but i use them all the time in administrative items.

Second, you can find out what EM uses by running profiler when you rfresh your display. It uses queries, I believe so the math may differ. The only way to verify things is to calculate the table size bsaed on each row. I'd pick one way and use it consistantly, then you will know about changes and it will mean something.

Steve Jones

steve@dkranch.net

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 5

You can issue a couple different commands to make sure you are getting correct information.

dbcc updateusage

or

sp_spaceused 'table_name','true'

the true in the second command indicates you want to update usage.

Hope this helps

Tom Goltl