3

How can I calculate the total no. of records in a table? I want to show all table names in a DB along with the no. of records in each table

nos
  • 223,662
  • 58
  • 417
  • 506

4 Answers4

6

The fastest method is:

proutil dbname -C tabanalys > dbname.tab

this is an external utility that analyzes the db.

You can also, of course read every record and count them but that tends to be a lot slower.

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
5

The way to get the number of records depends on the application you are planning.

Our DBAs just use the progress utilities. In Unix /usr/dlc/bin/proutil -C dbanalys or some variation to get database information and just dump that to a file.

To get the schema information from progress itself you can use the VST tables. Specifically within a particular database you can use the _file table to retrieve all of the table names.

Once you have the table names you can use queries to get the number of records in the table. The fastest way to query a particular table for a record count is to use the preselect.

This will require the usage of a dynamic buffer and query.

So you can do something like the following.

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:

    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().

    DISP _file._file-name h_qry:NUM-RESULTS.

    DELETE OBJECT h_qry.
    DELETE OBJECT h_buffer.

END.
sherbang
  • 15,696
  • 1
  • 22
  • 16
DuStorm
  • 293
  • 2
  • 7
1

An easy one:

Select count(*) from tablename.

A bit more complex:

Def var i as int.
for each table:
    i = i + 1.
end.
display i.

For more complex answer, you got the others.

asteri
  • 11,402
  • 13
  • 60
  • 84
Carlos
  • 21
  • 1
  • I think this is the best answer, but it is worth noting to add a no-lock statement onto the foreach if you are only counting. not necessary but a good habit for transactional tables... so: for each table no-lock: – elzaer Jan 12 '15 at 05:07
-1

Use CURRENT-RESULT-ROW function with DEFINE QUERY and GET LAST to get the total number of records:

e.g.

DEFINE QUERY qCustomer FOR Customer SCROLLING.
OPEN QUERY qCustomer FOR EACH Customer NO-LOCK.

GET LAST qCustomer.
DISPLAY CURRENT-RESULT-ROW("qCustomer") LABEL "Total number of rows".
...
CLOSE QUERY qCustomer.
CuberChase
  • 4,458
  • 5
  • 33
  • 52
CSY
  • 208
  • 1
  • 9