Getting a List of DB Tables with sizes

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

SQL Server 2000 Forums

SQL Server Development (2000)

Getting a List of DB Tables with sizes

Author

Topic

DionDKB
Starting Member

3 Posts

Posted - 2003-04-29 : 03:59:56

Hi,

Is it possible to get a list of tables with their relative sizes for MS SQL 6.5?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-04-29 : 06:01:04

Hi!

You can loop through the tables (for example from information_schema.tables) and run sp_spaceused 'table'. This will show you the size of the table.

Edited by - andraax on 04/29/2003 06:01:23

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-04-29 : 06:09:47

Oops... Information_schema views did not exist in 6.5 did they... Try this script:

declare @tn varchar(200),
@sql varchar(500)

create table #res
(
name varchar(200),
numrows int,
reserved varchar(200),
data varchar(200),
index_size varchar(200),
unused varchar(200)
)

declare tabcur cursor for
select name
from sysobjects (nolock)
where type='U'

open tabcur
fetch from tabcur into @tn

while @@fetch_status = 0
begin
select @sql = 'insert #res exec sp_spaceused ''' + @tn + ''''
exec (@sql)
fetch from tabcur into @tn
end

close tabcur
deallocate tabcur

select * from #res
drop table #res

DionDKB
Starting Member

3 Posts

Posted - 2003-04-29 : 06:33:44

By the way, the recordcount would be just as good.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-29 : 07:08:06

DionDKB...."By the way, the recordcount would be just as good."
not quite true....

The question asked for size...and size <> volume

area = height * width

an equivalent for the situation here would be
size = volume * width

recordcount only gives volume
recordlength will (nearly) give width

2 tables...a) with 10000 records of 100 characters and b) with 5000 records of 400 characters

table b) will be larger (in size) in theory that table a)...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-29 : 09:17:37

here is something that worked in SQL7 ... don't know about SQL6.5

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-04-29 : 09:54:15

With the script i posted you get both size and number of rows....

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-04-29 : 12:33:36

And you can take a look at the article we wrote on this: http://www.sqlteam.com/item.asp?ItemID=282

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources