Script needed
Maxine E. Sarjeant wanted a report of column count and record length per table, and got it by summing syscolumns collength for tabid above 99. Norman Erickson Lugtu and Jonathan Leffler pointed to the ncols and rowsize columns in systables instead. Jonathan warned that summing collength is wrong for DECIMAL, MONEY, DATETIME, INTERVAL and variable-length VARCHAR columns. Answered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello all, I need to report on all the tables in our production database and break down the information per table to include the number of columns and the total record length. I was going to use the syscolumns table to generate the column and record length information and was wondering if anyone out there already has a similar script written. TIA, Maxine E. Sarjeant Sent via Deja.com http://www.deja.com/
I realize I didn't say we're using IDS 7.3, etc.
but now I've got the information I needed using the following:
database uidb;
select systables.tabname,
count (syscolumns.colname),
sum (syscolumns.collength)
from systables,
syscolumns
where syscolumns.tabid > 99
and syscolumns.tabid = systables.tabid
group by tabname;
> In article <95c918$22e$1@nnrp1.deja.com>,
> Maxine E. Sarjeant <btimes@uis.doleta.gov> wrote:
> Hello all,
> I need to report on all the tables in our production database and
> break down the information per table to include the number of
> columns and the total record length. I was going to use the syscolumns
> table to generate the column and record length information and was
> wondering if anyone out there already has a similar script written.
> TIA,
> Maxine E. Sarjeant
>
> Sent via Deja.com
> http://www.deja.com/
>
--
============================================
work: btimes@uis.doleta.gov, aim: max1nes
home: msarjeant@yahoo.com, aim: mesarjeant
homepage: http://www.geocities.com/msarjeant/
Sent via Deja.com
http://www.deja.com/
You don't have to use syscolumns table in your query. Use this instead:
database uidb;
select tabname,
ncols,
rowsize
from systables
where tabid > 99 and
tabtype = 'T'
order by 1;
Erickson
In article <95cges$9cq$1@nnrp1.deja.com>,
Maxine E. Sarjeant <btimes@uis.doleta.gov> wrote:
> I realize I didn't say we're using IDS 7.3, etc.
> but now I've got the information I needed using the following:
>
> database uidb;
> select systables.tabname,
> count (syscolumns.colname),
> sum (syscolumns.collength)
> from systables,
> syscolumns
> where syscolumns.tabid > 99
> and syscolumns.tabid = systables.tabid
> group by tabname;>
> > In article <95c918$22e$1@nnrp1.deja.com>,
> > Maxine E. Sarjeant <btimes@uis.doleta.gov> wrote:
> > Hello all,
> > I need to report on all the tables in our production database and
> > break down the information per table to include the number of
> > columns and the total record length. I was going to use the
syscolumns
> > table to generate the column and record length information and was
> > wondering if anyone out there already has a similar script written.
> > TIA,
> > Maxine E. Sarjeant
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> --
> ============================================
> work: btimes@uis.doleta.gov, aim: max1nes
> home: msarjeant@yahoo.com, aim: mesarjeant
> homepage: http://www.geocities.com/msarjeant/
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/
"Maxine E. Sarjeant" wrote:
> I need to report on all the tables in our production database and
> break down the information per table to include the number of
> columns and the total record length. I was going to use the syscolumns
> table to generate the column and record length information and was
> wondering if anyone out there already has a similar script written.
SELECT owner, tabname, ncols, rowsize from "informix".systables;
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
"Maxine E. Sarjeant" wrote:
> I realize I didn't say we're using IDS 7.3, etc.
> but now I've got the information I needed using the following:
>
> database uidb;
> select systables.tabname,
> count (syscolumns.colname),
> sum (syscolumns.collength)
That won't work if you have any DECIMAL, MONEY, DATETIME or INTERVAL
data types in your table -- the numbers will be wildly too large. Or if
you have a VARCHAR column with a minimum space allocation (eg
VARCHAR(255,30)).
> from systables,
> syscolumns
> where syscolumns.tabid > 99
> and syscolumns.tabid = systables.tabid
> group by tabname;
>
> > Maxine E. Sarjeant <btimes@uis.doleta.gov> wrote:
> > I need to report on all the tables in our production database and
> > break down the information per table to include the number of
> > columns and the total record length. I was going to use the syscolumns
> > table to generate the column and record length information and was
> > wondering if anyone out there already has a similar script written.
Use the rowsize information from systables.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
PS: Apologies if this gets double-posted.