Get row counts of all tables in a database

Hi, Our Informix environment has two DB instances (IDS V9.4) and each instance has multiple databases. My requirement is to get the rowcounts of all tables in all databases ( over 150 databases ) in both the instances. Any help regarding this will be greatly appreciated. Thanks

I think this will be close to what you want. select trim(dbsname)||":"||trim(tabname) AS Name, sum(nrows) AS count from sysmaster:sysptnhdr P, sysmaster:systabnames T where P.partnum = T.partnum group by P.lockid , 1 order by 2 desc John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/15/2010 03:06:26 PM: > [image removed] > > Get row counts of all tables in a database [18696]> > > SANTOSH BANGALORE > > to: > > ids > > 01/15/2010 03:07 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, > > Our Informix environment has two DB instances (IDS V9.4) and each > instance has > multiple databases. My requirement is to get the rowcounts of all tables in > all databases ( over 150 databases ) in both the instances. > Any help regarding this will be greatly appreciated. > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

The following shell script: #!/usr/bin/ksh dbaccess sysmaster - <<EOF select dbsname, tabname, sum( nrows ) from systabnames st, sysptnhdr sp where st.partnum = sp.partnum group by 1, 2 order by 1, 2;EOF Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jan 15, 2010 at 6:06 PM, SANTOSH BANGALORE <santosh.bn@hotmail.com>wrote: > Hi, > > Our Informix environment has two DB instances (IDS V9.4) and each instance > has > multiple databases. My requirement is to get the rowcounts of all tables in > all databases ( over 150 databases ) in both the instances. > Any help regarding this will be greatly appreciated. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce03d0e8cc84e047d50c9a5