Script needed

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.