Re: finding the large tables
wasef wrote:
>
> In article <85dnmq$m21$1@calais.pt.lu>, "Andrew Pearson"
> <apearson@pt.lu> wrote:
> > Phillip,
> > This is a reasonably straighforward one from the
> > sysmaster
> > database or from systables, if you just want number of
> > rows
> > then try something like:
> > SELECT tabname, nrows
> > FROM systables
> > ORDER BY 2 DESC> > you could also factor in the rowsize.
> > Have a look at deja news and so on, the regular
> > contributors
> > to this newsgroup have written a lot of handy
> > scripts/selects that you could use.
> > Andrew.
> > Phillip Tien <phillip.tien@wholefoods.com> wrote in
> > message
> > news:387A5A8D.D56189D9@wholefoods.com...
> > > Anybody got a SQL script handy to find the largest
> > tables
> > in a database
> > > (i.e. largest no. of rows) without having to hit each
> > table manually?
> > > In other words, one script that gets run once and
> > returns
> > the two
> > > largest tables in a database.
> > >
> > > --
> > > Phillip
> > >
> > >
> But this shows only the sysmaster tables, what about my
> database???
>
> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
How about:
database sysmaster;
select tab.dbsname, tab.tabname, hdr.nrows, hdr.npused
from systabnames tab, sysptnhdr hdr
where tab.partnum = hdr.partnum
order by 3 desc
--
John Carlson
Informix DBA
WHSmith USA
#include std_disclaimer.h /* These are my opinions, not my company's
opinion */