January 17, 2002 at 2:41 pm
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
January 17, 2002 at 3:23 pm
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
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 17, 2002 at 3:25 pm
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
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 17, 2002 at 3:49 pm
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.
January 21, 2002 at 12:34 pm
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 21, 2002 at 2:58 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply