SEO

#1 (**permalink**) 04-08-2008, 03:22 PM

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

jlknowlton@hotmail.com (James Knowlton) wrote in message news:<bde3b38b.0403310605.3ea7b6d1@posting.google. com>...
> psankar@ibc.ca (Praim Sankar) wrote in message news:<57ad4eb6.0403301259.6849bb5@posting.google.c om>...
> > 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@ibc.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

SEO

Re: Find the size of all tables in a database