Answered list with table names and record count

Jack Brosch

New Member
Does anybody have the syntax to scan SYSTABLES to create a text file or cursor or something that has all the table names and record counts? Have a VERY poorly designed database with around 4,000 tables and I'm trying to figure out what changes when I enter an order.
Thanks
 

tamhas

ProgressTalk.com Sponsor
Record counts are not maintained in an Open Edge database. Look up proutil -C dbanalys
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Does anybody have the syntax to scan SYSTABLES to create a text file or cursor or something that has all the table names and record counts? Have a VERY poorly designed database with around 4,000 tables and I'm trying to figure out what changes when I enter an order.
Thanks

As Thomas said you can run dbanalys or tabanalys before and after you run the application and parse the record counts to get the delta on each table. A bit laborious, but doable. You can also write some ABL code (assuming you have a development license) to iterate over _File to get a list of application tables, build from that a SQL script to get record counts from each table, and then parse the data returned when you run that script in your SQL client of choice. Either approach will get you the same data: record counts in each table.

This will not give you what you said you are after: knowing what your application is doing. Processing an order could involve deleting a row in table A and then creating a row, and its record count will be the same in the before and after snapshots. Also, you could have one or many tables whose records are updated, rather than created or deleted. They are a part of "what changes" in the database when you process an order but the record counts don't change. So in this analysis they are indistinguishable from tables that are not touched. This approach is insufficient.

I assume you can't just determine the application logic directly by reading the source, or you wouldn't be taking this roundabout approach.

It would be helpful to know more about your situation. For example:
  • What is your Progress version number?
  • What is changing your database data? A SQL application? An ABL application? Both?
  • Do you have a development license or can you only run vendor-provided r-code?
  • Do you have access to unencrypted application source?
  • Can you write ABL code?
  • Can you stop/start your database and change your database startup parameters?
  • It this a test system/database where you can increase logging verbosity or break into the code with a debugger without causing business impact?
  • Do you have exclusive access to this database?
  • Is this a "toy" database with production schema but minimal data, or is it comparable in size to production?
  • Does this application (if it is ABL and it is not prod) have the same code and PROPATH as production?
  • Once you learn more about what the application is doing, what do you hope to do with this information?
There are several things you can do to try to approach the task of finding out more about what the application does. Whether you can do some of them depends on the answers to the questions above.

Also, you said the database is very poorly designed. Do you wish to address this issue as well? If so are you referring to schema design, structure design, or both?
 
Top