Report User Tables and rows
How do I report all the user tables and a count of the total rows in each user table e.g Table No. of Rows ------ ----------- employee 350 address 600
SELECT st.tabname, sp.nrows
FROM systables st, sysmaster:sysptnhdr sp
WHERE st.partnum = sp.partnum
AND st.tabid >= 100 AND st.partnum != 0
UNION
SELECT st.tabname, SUM(sp.nrows) nrows
FROM systables st, sysfragments sf, sysmaster.sysptnhdr sp
WHERE st.partnum = 0
AND st.tabid = sf.tabid
AND sf.partnum = sp.partnum
GROUP BY 1
ORDER BY 1;
The first part of the UNION handles non-fragmented tables and the second
part any fragmented tables.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Tue, Oct 6, 2009 at 7:10 AM, STEPHEN LIDDLE <stephen.liddle@assetcods.com
> wrote:
> How do I report all the user tables and a count of the total rows in each
> user
> table
>
> e.g Table No. of Rows
>
> ------ -----------
>
> employee 350
>
> address 600
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd7608a0f4e0475433942
Brilliant Thanks for your help with this Art