I have a list of 2500+ table files from an application in a WF domain that I need to go through. Several of the tables do not have any records on them. Is there a quick way to use the SYSFILES/SYSTABLE retrieval method and get a record/line count to be able to seperate the tables that do and do not have data on them?

Example: table name record count

This message has been edited. Last edited by: j42p11,

What sort of files are they ?

.FOC, .FTM, something else ?

SYSFILES will give you a list of all the MASTER files in the search path.
From this you can isolate the files from your specific application (if you mean that "application" is a WF application.
If these file don't need a FILEDEF then you can SAVE the names of the files and use DM to loop through the names, issue a COUNT * for each, check whether any records were retrieved and print out a list.
If a FILEDEF is necessary, then you will have to specify it.

Waz - The table types are SQLMSS with MAS extension.

Danny-SRL - When I said application, I was refering to a completely seperate program that the tables are from. I can isolate them as the location on the SYSTABLE is specific to those files. I haven't ever done a DM loop reading records out of a file. I tried searching the form to get some sample loop code but haven't really found much. How would I go about doing something like that?

Assuming the stats are up to date, you could try to query the DB for the number of rows.

That sounds like what I am looking for. How would I do something like that on a mass level? I can do this on a one by one basis but not sure how to set up a looping program to go through them all? Any help is greatly appreciated.

Where are probaly a lot of ways to do this.

I did a Google search for SQL server row count, and found this.

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

They say that it potentially isn't accurate, as it works on the indexes.

How accurate does this need to be ?

So I ended up enlisting the assistance of another report person here at my work. We ended up coming up with the following code. We never were able to get it far enough along to save the data to a file but I was able to do a work around with this to get what I needed.

TABLE FILE SYSTABLE
PRINT * AND COMPUTE
CNT1/I5 = IF CNT1 EQ 0 THEN 1 ELSE CNT1 + 1;
ON TABLE HOLD AS TABLELST
-*WHERE RECORDLIMIT EQ 300
END

-SET &LOOPCNT = &RECORDS + 1;

-SET &CNT = 1;
-SET &WFDESCRIBE = ON;
-RUN

-REPEATLOOP

DEFINE FILE TABLELST
CNT2/I5 = IF CNT2 EQ 0 THEN 1 ELSE CNT2 + 1;
END
TABLE FILE TABLELST
PRINT NAME CNT1 CNT2
BY CNT1 NOPRINT
WHERE CNT1 EQ &CNT
ON TABLE HOLD AS LSTOTBLS FORMAT ALPHA
WHERE RECORDLIMIT EQ 1
END
-RUN

-READ LSTOTBLS &TABLE.A64.
-RUN

-TYPE TABLE = &TABLE

TABLE FILE &TABLE
PRINT *
ON TABLE HOLD AS NEWTABLE
WHERE RECORDLIMIT EQ 300
END
-RUN
-SET &RCRDCNT = &RECORDS;
-SET &CNT = &CNT + 1;
-SET &TABLECNT = &TABLE || &CNT;
-RUN
-TYPE TABLECNT = &TABLECNT
-TYPE RECORDS = &RCRDCNT
-TYPE &TABLE = &RCRDCNT

-IF &CNT = &LOOPCNT THEN GOTO EXITLOOP ELSE GOTO REPEATLOOP

-EXITLOOP
-EXIT



The work around was copying all the text, pasting it into excel, sort to remove all the lines but (-TYPE &TABLE = &RCRDCNT). Did a text to columns and put the table name next to record count. Since I had so many records I did a max limit of 300 read records per table, so anything that had more than 300 showed as 300. Ended up solving my problem. If anyone else is smart enough to convert the outputs into a file that can be saved into excel, that would make this all the better.

Consider this, as another (similar) approach:

FILEDEF REC_COUNT DISK C:\IBI\APPS\FOCALPOINT\REC_COUNT.TXT
-RUN
TABLE FILE SYSTABLE
PRINT NAME
WHERE RECORDLIMIT EQ 10
ON TABLE HOLD AS TABLENAMES FORMAT ALPHA
END
-RUN
-SET &LOOPCNTR = 0 ;
-TYPE *** Counting Records in &RECORDS Tables...
-REPEAT ENDOFLOOP &RECORDS TIMES ;
-READ TABLENAMES NOCLOSE &TABLENAME.A64. 
-SET &TABLENAME=UPCASE(64,&TABLENAME,&TABLENAME) ;
-RUN
TABLE FILE &TABLENAME
PRINT *
ON TABLE HOLD
END
-RUN
-TYPE *** &RECORDS records in &TABLENAME 
-WRITE REC_COUNT NOCLOSE &RECORDS records in &TABLENAME 
-ENDOFLOOP

Of course you can format the output line,

-WRITE REC_COUNT NOCLOSE &RECORDS records in &TABLENAME,

anyway you desire...