Count Records in each table of a Database

Smud · Feb 9, 2018

Saw this question recently, this is better than "for each ...: i = i + 1. end. display i." type of code;

Code:

CREATE WIDGET-POOL.
DEF VAR h_predicate AS CHAR     NO-UNDO.
DEF VAR h_qry       AS HANDLE   NO-UNDO.
DEF VAR h_buffer    AS HANDLE   NO-UNDO.
FOR EACH _file NO-LOCK:
    if _file._file-name begins "_" then next.
    if _file._file-name begins "sys" then next.
    h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK".
    CREATE BUFFER h_buffer FOR TABLE _file._file-name .
    CREATE QUERY h_qry.
    h_qry:SET-BUFFERS( h_buffer ).
    h_qry:QUERY-PREPARE( h_predicate ).
    h_qry:QUERY-OPEN().
    DISPLAY _file._file-name h_qry:NUM-RESULTS.
    DELETE OBJECT h_qry.
    DELETE OBJECT h_buffer.
END.

Try it in the dojo

jdpjamesp · Feb 9, 2018

Probably makes no difference but I would h_qry:query-close before deleting.
Also, query-prepare returns a logical as to the success. So you can

Code:

if not h_qry:QUERY-PREPARE( h_predicate ) then next.

Or something along those lines (you still have to delete the query object of course) to avoid the query open and subsequent lines throwing runtime errors if the prepare string fails.

Rob Fitzpatrick · Feb 9, 2018

However you count records from ABL, it is slow for large tables. In my testing (several years ago) it was faster to code-gen and then execute a SQL script to count the records in each table.

Another alternative is table analysis: proutil db -C tabanalys > db.tba. Since it can be run on a per-area basis, it can be useful to count the records in a particularly large table that is in its own area. It might also just be faster to run it on the whole database and then parse its output to get the record counts. The CSV output option in 11.4+ makes this much easier.

Smud · Feb 10, 2018

Cringer said:
Probably makes no difference but I would h_qry:query-close before deleting.
Also, query-prepare returns a logical as to the success. So you can

Code:

if not h_qry:QUERY-PREPARE( h_predicate ) then next.

Or something along those lines (you still have to delete the query object of course) to avoid the query open and subsequent lines throwing runtime errors if the prepare string fails.

Yep that works! I have a few large conversions to run over the next few months and it will help knowing how many rows I'm dealing with etc.

I'll try the proutil suggestion next week as well - thanks Rob - that will help in planning.

Rob Fitzpatrick · Feb 10, 2018

Here is a recent thread on Community that is related:
How many records in a table? - Forum - OpenEdge RDBMS - Progress Community

Smud · Feb 13, 2018

slightly restructured, its not too bad for speed;

Code:

CREATE WIDGET-POOL.
DEF VAR h_predicate AS CHAR     NO-UNDO.
DEF VAR h_qry       AS HANDLE   NO-UNDO.
DEF VAR h_buffer    AS HANDLE   NO-UNDO.

/* predicate with working database */
FOR EACH _file NO-LOCK:
    if _file._file-name begins "_" then next.
    if _file._file-name begins "sys" then next.
    h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK".
    CREATE BUFFER h_buffer FOR TABLE _file._file-name.
    CREATE QUERY h_qry.
    h_qry:SET-BUFFERS( h_buffer ).
    if h_qry:QUERY-PREPARE( h_predicate ) then do:
      h_qry:QUERY-OPEN().
      display _file._file-name h_qry:NUM-RESULTS.
    end.
    h_qry:query-close.
    DELETE OBJECT h_qry.
    DELETE OBJECT h_buffer.
END.

ricardo.m.goncalves · Sep 6, 2018

Hello Smud.

Can help me to use your code to count all records on tables of schema area?

Schema area can be found using this code.

Code:

/* a list of storage areas and their associated tables: */

FOR EACH _storageobject WHERE _storageobject._object-type = 1
    AND _storageobject._Object-number GE 1 NO-LOCK
                            by _area-number
                            by _object-type:
FIND _File WHERE _File._File-Number = _storageobject._Object-number NO-LOCK NO-ERROR.
FIND _area WHERE _area._area-number = _storageobject._area-number  NO-LOCK NO-ERROR.
DISPLAY
_File-Number FORMAT "->>9"  LABEL "T#"
_File-name FORMAT "x(25)" LABEL "Table"
_area._area-number FORMAT ">9" LABEL "A#"
_area._area-name FORMAT "X(25)" LABEL "Area" SKIP     .
END.

Tks.

Rob Fitzpatrick · Sep 6, 2018

ricardo.m.goncalves said:
Can help me to use your code to count all records on tables of schema area?

You can write code to do it but this is quicker:

Pre-11.4: proutil sports -C tabanalys area "schema area" > sp_sch.tba
11.4+: proutil sports -C dbanalys area "schema area" -csOutput -verbose and then look in "sports.tab_6.txt".

Smud · Sep 8, 2018

Have not tried this but it could also be useful to see how the schema areas are being used; I'll let you know how it goes;

Smud · Sep 8, 2018

Now that I remember the tags;

Code:

CREATE WIDGET-POOL. 
DEF VAR h_predicate AS CHAR     NO-UNDO. 
DEF VAR h_qry       AS HANDLE   NO-UNDO. 
DEF VAR h_buffer    AS HANDLE   NO-UNDO. 
 
DEF VAR pv-TotalbyArea AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-GrandTotal  AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-Area        AS CHARACTER NO-UNDO FORMAT "x(35)". 
DEF VAR pv-Result      AS DECIMAL   NO-UNDO FORMAT ">>>,>>>,>>9". 
 
FORM pv-Area pv-Result WITH DOWN FRAME f-Main. 
 
FOR EACH _storageobject WHERE _storageobject._object-type = 1 
    AND _storageobject._Object-number GE 1 NO-LOCK 
                            BREAK by _storageobject._area-number 
                            by _storageobject._object-type: 
 
    IF FIRST-OF(_storageobject._area-number) THEN pv-TotalbyArea = 0. 
 
FIND _File WHERE _File._File-Number = _storageobject._Object-number NO-LOCK NO-ERROR. 
FIND _area WHERE _area._area-number = _storageobject._area-number  NO-LOCK NO-ERROR. 
 
    if _file._file-name begins "_" then next. 
    if _file._file-name begins "sys" then next. 
    h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK". 
    CREATE BUFFER h_buffer FOR TABLE _file._file-name. 
    CREATE QUERY h_qry. 
    h_qry:SET-BUFFERS( h_buffer ). 
    if h_qry:QUERY-PREPARE( h_predicate ) then do: 
      h_qry:QUERY-OPEN(). 
      // display _file._file-name @ pv-Area  
      //    h_qry:NUM-RESULTS @ pv-Result WITH FRAME f-Main. 
      // down 1 with frame f-Main. 
 
      ASSIGN pv-TotalbyArea = pv-TotalbyArea + h_qry:NUM-RESULTS 
             pv-GrandTotal  = pv-GrandTotal  + h_qry:NUM-RESULTS. 
 
    end. 
    h_qry:query-close. 
    DELETE OBJECT h_qry. 
    DELETE OBJECT h_buffer. 
 
IF LAST-OF(_storageobject._area-number) THEN DO: 
    DISPLAY _storageobject._area @ pv-Area pv-TotalByArea @ pv-Result  
    WITH FRAME f-Main. 
    DOWN 1 WITH FRAME f-Main. 
END. 
 
END. 
 
DISPLAY "Total" @ pv-Area pv-GrandTotal @ pv-Result  
    WITH FRAME f-Main.

ricardo.m.goncalves · Sep 9, 2018

Tks friend.

Smud · Sep 10, 2018

I'm surprised nobody picked me up on the "bad" code - all too nice perhaps? But you should never do a "next" before "last-of" processing ;-)

Seems to work OK (if not a little slow) but only one DB at a time, prefix _storageobject, _File and _area with the DB name in case of ambiguity. Not an expert in storage areas - sorry.

Code:

CREATE WIDGET-POOL. 
DEF VAR h_predicate AS CHAR     NO-UNDO. 
DEF VAR h_qry       AS HANDLE   NO-UNDO. 
DEF VAR h_buffer    AS HANDLE   NO-UNDO. 
 
DEF VAR pv-TotalbyArea AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-GrandTotal  AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-Area        AS CHARACTER NO-UNDO FORMAT "x(35)". 
DEF VAR pv-Result      AS DECIMAL   NO-UNDO FORMAT ">>>,>>>,>>9". 
 
FORM pv-Area pv-Result WITH DOWN FRAME f-Main. 
 
FOR EACH _storageobject WHERE _storageobject._object-type = 1 
    AND _storageobject._Object-number GE 1 NO-LOCK 
                            BREAK by _storageobject._area-number 
                            by _storageobject._object-type: 
 
    IF FIRST-OF(_storageobject._area-number) THEN pv-TotalbyArea = 0. 
 
    FIND _File WHERE _File._File-Number = _storageobject._Object-number NO-LOCK NO-ERROR. 
    FIND _area WHERE _area._area-number = _storageobject._area-number  NO-LOCK NO-ERROR. 
 
    if NOT(_file._file-name begins "_") AND NOT(_file._file-name begins "sys") then DO: 
      h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK". 
      CREATE BUFFER h_buffer FOR TABLE _file._file-name. 
      CREATE QUERY h_qry. 
      h_qry:SET-BUFFERS( h_buffer ). 
      if h_qry:QUERY-PREPARE( h_predicate ) then do: 
        h_qry:QUERY-OPEN(). 
        // display _file._file-name @ pv-Area  
        //    h_qry:NUM-RESULTS @ pv-Result WITH FRAME f-Main. 
        // down 1 with frame f-Main. 
 
        ASSIGN pv-TotalbyArea = pv-TotalbyArea + h_qry:NUM-RESULTS 
               pv-GrandTotal  = pv-GrandTotal  + h_qry:NUM-RESULTS. 
 
      end. 
      h_qry:query-close. 
      DELETE OBJECT h_qry. 
      DELETE OBJECT h_buffer. 
    END. 
     
    IF LAST-OF(_storageobject._area-number) THEN DO: 
      DISPLAY _area._area-name @ pv-Area pv-TotalByArea @ pv-Result  
        WITH FRAME f-Main. 
      DOWN 1 WITH FRAME f-Main. 
    END. 
 
END. 
 
DISPLAY "Total" @ pv-Area pv-GrandTotal @ pv-Result  
    WITH FRAME f-Main.

Smud · Sep 10, 2018

Output from the dojo;

pv-Area pv-Result
----------------------------------- -----------
Employee 192
Inventory 8,446
Cust_Data 1,117
Order 17,928
Misc 238
Total 27,921

ricardo.m.goncalves · Sep 10, 2018

Yes, i see it.
But, and if i want to show the records of all tables of area 6 for exemple?

Rob Fitzpatrick · Sep 10, 2018

ricardo.m.goncalves said:
Yes, i see it.
But, and if i want to show the records of all tables of area 6 for exemple?

The _storageobject table has an _area-number field. You could add "and _storageobject._area-number = 6" to the query.

Rob Fitzpatrick · Sep 10, 2018

Smud said:
Seems to work OK (if not a little slow) but only one DB at a time, prefix _storageobject, _File and _area with the DB name in case of ambiguity.

If you wanted info from multiple DBs at once, that would require a wrapper procedure that iterates over the connected databases, sets the "dictdb" alias for each target database in turn, and runs the query procedure. In that case, the tables should be qualified with "dictdb.", not the logical database name. That deals with the system table name ambiguity.

Smud · Sep 11, 2018

Thanks Rob, I also saw somewhere it done using a temp table to combine the _storagobject data but your suggestion is better I believe. You could also detail each file and area - Run on the Dojo again (can be cleaned up, but you get the idea);

Code:

pv-Area                               pv-Result
----------------------------------- -----------

File:Employee                                55
File:TimeSheet                               25
File:Vacation                                12
File:Family                                  72
File:Department                               7
File:Benefits                                21
Area:Employee                               192

File:Item                                    55
File:Supplier                                10
File:Warehouse                               14
File:Bin                                    770
File:InventoryTrans                          75
File:PurchaseOrder                        2,129
File:POLine                               5,337
File:SupplierItemXref                        56
Area:Inventory                            8,446

File:Customer                             1,117
Area:Cust_Data                            1,117

File:OrderLine                           13,970
File:ShipTo                                   3
File:BillTo                                   2
File:Order                                3,953
Area:Order                               17,928

File:Invoice                                147
File:Salesrep                                 9
File:State                                   51
File:LocalDefault                            10
File:RefCall                                 13
File:Feedback                                 8
Area:Misc                                   238

Total                                    27,921

Code:

CREATE WIDGET-POOL. 
DEF VAR h_predicate AS CHAR     NO-UNDO. 
DEF VAR h_qry       AS HANDLE   NO-UNDO. 
DEF VAR h_buffer    AS HANDLE   NO-UNDO. 
 
DEF VAR pv-TotalbyArea AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-GrandTotal  AS DECIMAL NO-UNDO INITIAL 0. 
DEF VAR pv-Area        AS CHARACTER NO-UNDO FORMAT "x(35)". 
DEF VAR pv-Result      AS DECIMAL   NO-UNDO FORMAT ">>>,>>>,>>9". 
 
FORM pv-Area pv-Result WITH DOWN FRAME f-Main. 
 
FOR EACH _storageobject WHERE _storageobject._object-type = 1 
    AND _storageobject._Object-number GE 1 NO-LOCK 
                            BREAK by _storageobject._area-number 
                            by _storageobject._object-type: 
 
    IF FIRST-OF(_storageobject._area-number) THEN Do:
      pv-TotalbyArea = 0. 
      down 1 with frame f-Main.
    END.
 
    FIND _File WHERE _File._File-Number = _storageobject._Object-number NO-LOCK NO-ERROR. 
    FIND _area WHERE _area._area-number = _storageobject._area-number  NO-LOCK NO-ERROR. 
 
    if NOT(_file._file-name begins "_") AND NOT(_file._file-name begins "sys") then DO: 
      h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK". 
      CREATE BUFFER h_buffer FOR TABLE _file._file-name. 
      CREATE QUERY h_qry. 
      h_qry:SET-BUFFERS( h_buffer ). 
      if h_qry:QUERY-PREPARE( h_predicate ) then do: 
        h_qry:QUERY-OPEN(). 
        display "File:" + _file._file-name @ pv-Area  
           h_qry:NUM-RESULTS @ pv-Result WITH FRAME f-Main. 
        down 1 with frame f-Main. 
 
        ASSIGN pv-TotalbyArea = pv-TotalbyArea + h_qry:NUM-RESULTS 
               pv-GrandTotal  = pv-GrandTotal  + h_qry:NUM-RESULTS. 
 
      end. 
      h_qry:query-close. 
      DELETE OBJECT h_qry. 
      DELETE OBJECT h_buffer. 
    END. 
     
    IF LAST-OF(_storageobject._area-number) THEN DO: 
      DISPLAY "Area:" + _area._area-name @ pv-Area pv-TotalByArea @ pv-Result  
        WITH FRAME f-Main. 
      DOWN 1 WITH FRAME f-Main. 
    END. 
 
END. 
DOWN 1 WITH FRAME f-Main.
DISPLAY "Total" @ pv-Area pv-GrandTotal @ pv-Result  
    WITH FRAME f-Main.

ricardo.m.goncalves · Sep 11, 2018

Perfect, tks.

Count Records in each table of a Database

Smud

Member

jdpjamesp

ProgressTalk.com Moderator

Rob Fitzpatrick

ProgressTalk.com Sponsor

Smud

Member

Rob Fitzpatrick

ProgressTalk.com Sponsor

Smud

Member

ricardo.m.goncalves

New Member

Rob Fitzpatrick

ProgressTalk.com Sponsor

Smud

Member

Smud

Member

ricardo.m.goncalves

New Member

Smud

Member

Smud

Member

ricardo.m.goncalves

New Member

Rob Fitzpatrick

ProgressTalk.com Sponsor

Rob Fitzpatrick

ProgressTalk.com Sponsor

Smud

Member

ricardo.m.goncalves

New Member