CODEWITHSUNDEEP
openedgeprogress-4glprogress-db4gl
user3668036
user3668036

Reputation: 25

progress 4gl query for export count of records in all table available in db

I am trying to export count of all tables in excel or text file.

if any program or any query will help me ?

I am trying to code for export count of data available in each table.

code:

  define stream table t1.
    output stream t1 to t1.csv.

    &scope-define display-fields count(*)

    select count(*) from emp.

    export starem t1 delimiter ",".

This code create excel with empty value but display result on screen. i out in excel.

Upvotes: 0

Views: 1842

Answers (1)

Jensd
Jensd

Reputation: 8011

Unsure what you want to do. Something like this if you want to count the number of tables in a database:

DEFINE VARIABLE icount AS INTEGER NO-UNDO.
FOR each _file NO-LOCK WHERE _file._owner = "PUB":

    /* Skip "hidden" virtual system tables */
    IF _file._file-name BEGINS "_" THEN NEXT.

    iCount = iCount + 1.
END.

MESSAGE iCount "tables in the database"
    VIEW-AS ALERT-BOX INFORMATION.

If you have several db's connected you need to prepend the _file-table with the database name ie database._file.

However: since you say "export to excel" perhaps what you mean is that you want to know the number of records for each table?

To count number of records in a table you can use FOR or SELECT.

SELECT COUNT(*) FROM tablename.

or

DEFINE VARIABLE iCount AS INTEGER NO-UNDO.  
FOR EACH tablename NO-LOCK TABLE-SCAN:
    iCount = iCount + 1.
END.
DISPLAY iCount.

If you don't want to code this for each table you need to combine it with a dynamic query counting all records.

DEFINE VARIABLE hQuery  AS HANDLE    NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE    NO-UNDO.
DEFINE VARIABLE iCount  AS INTEGER   NO-UNDO.
DEFINE VARIABLE cTable  AS CHARACTER NO-UNDO.

/* Insert tablename here */
cTable = "TableName".

CREATE QUERY hQuery.
CREATE BUFFER hBuffer FOR TABLE cTable.

hQuery:SET-BUFFERS(hBuffer).

hQuery:QUERY-PREPARE(SUBSTITUTE("FOR EACH &1", cTable)).

hQuery:QUERY-OPEN.

queryLoop:
REPEAT:

    hQuery:GET-NEXT().

    IF hQUery:QUERY-OFF-END THEN LEAVE queryLoop.

    iCount = iCount + 1.
END.

DELETE OBJECT hQuery.
DELETE OBJECT hBuffer.

MESSAGE iCount "records in the table".

Combine those two and you have a solution. It might be slow however since it will count all records of all tables.

A quick and dirty way is to run "tabanalys" on the database instead if you have access to it via the prompt:

proutil DatabaseName -C tabanalys > tabanalys.txt

This can be run online and might have impact on file io etc so run it the first time on off peak hours just to make sure. Then look into that file, you will see record count, sizes etc for all tables: system-tables as well as user-tables.

Proutil ran online might not be 100% correct but most likely "good enough".

Upvotes: 1

Related Questions