Re: Find the size of all tables in a database

#1

Disclaimer: I found this on one of the Sybase utility sites ran by end
users. This was 5 or 6 years ago and I can't
remember which one. I would give credit if I could remember where. Sorry!

Uncomment the commented line if you want to include tables with no entries.

Tom.
----------------------------------------------------------------------------
--------

select o.crdate, o.name, rowcnt(i.doampg) from sysobjects o, sysindexes i
where type = 'U' and o.id = i.id
/* and rowcnt(i.doampg)!= 0 */
order by rowcnt(i.doampg) desc, o.crdate, o.name
go

"Praim Sankar" <psankar (AT) ibc (DOT) ca> wrote

Quote:

I need to find out the size of all tables in a SYBASE database.
Does anyone have a script that will give me the number of rows and the
size of the table for each table in a database.

Thanks,

Praim Sankar
psankar (AT) ibc (DOT) ca

#2

Great and Thanks.
Regards,
Ram DSL,
London.
www.dslsoft.net

jlknowlton (AT) hotmail (DOT) com (James Knowlton) wrote in message news:<bde3b38b.0403310605.3ea7b6d1 (AT) posting (DOT) google.com>...

Quote:

psankar (AT) ibc (DOT) ca (Praim Sankar) wrote in message news:<57ad4eb6.0403301259.6849bb5 (AT) posting (DOT) google.com>...
I need to find out the size of all tables in a SYBASE database.
Does anyone have a script that will give me the number of rows and the
size of the table for each table in a database.

Thanks,

Praim Sankar
psankar (AT) ibc (DOT) ca

I cobbled the following together from several sources and am relying
on the information I got from those sources. This works pretty well
for me and I think it's accurate. I don't believe your page size
effects the results but I won't swear to it. The full script actually
records the information in a permenant table on a daily basis of ALL
the databases on a machine. I use the data to provide weekly reports
to management on the growth of our data warehouse and associated
databases. I hope this helps.

begin

declare @low_ integer

select @low_ = low
from master.dbo.spt_values
where number = 1
and type = "E"

if exists (select 1
from tempdb..sysobjects
where id = object_id('#pagecounts_')
and type = 'U')
drop table #pagecounts_

if exists (select 1
from tempdb..sysobjects
where id = object_id('#tableinfo_')
and type = 'U')
drop table #tableinfo_

select table_ = o.name,
low_ = @low_,
rowcount_ = rowcnt(i.doampg),
reserved_ = convert(numeric(20, 9), (reserved_pgs(i.id,
i.doampg) + reserved_pgs(i.id, i.ioampg))),
data_ = convert(numeric(20, 9), data_pgs(i.id, i.doampg)),
index_ = convert(numeric(20, 9), data_pgs(i.id, i.ioampg)),
unused_ = convert(numeric(20, 9), ((reserved_pgs(i.id, i.doampg)
+ reserved_pgs(i.id, i.ioampg)) - (data_pgs(i.id, i.doampg)
+ data_pgs(i.id, i.ioampg))))
into #pagecounts_
from sysobjects o,
sysindexes i
where i.id = o.id
and o.type = "U"

select distinct
date_ = getdate(),
database_ = db_name(db_id()),
table_,
row_count_ = sum(rowcount_),
data_kb_ = convert(numeric(11, 0), sum(data_) * (low_ /
1024)),
index_kb_ = convert(numeric(11, 0), sum(index_) * (low_ /
1024))
into #tableinfo_
from #pagecounts_
group by table_

select * from #tableinfo_

drop table #pagecounts_
drop table #tableinfo_
end
go

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Find the size of all tables in a database

comp.databases.sybase comp.databases.sybase

Re: Find the size of all tables in a database - 04-01-2004 , 03:03 AM

Re: Find the size of all tables in a database - 04-01-2004 , 09:41 AM