Number of rows in all tables

[Former Member]

Hi,

I need to query in max db to find number of rows exist in each and every table. Is there any query we can use to find in a single shot?

Example:

S.NO Table Name      Number of rows

1          A                    3000

2          B                     5000

[Former Member · Accepted solution]

Hi,

it depends on the database version you are using. In the newer database versions 7.8 and 7.9 you can use the system table files  to get the total number of records.

SELECT sum (entrycount) from files where type = 'TABLE'

This includes the MaxDb systemtables as well.
If you want to count the number of rows of one schema only use the following join command between tables and files:

SELECT sum(entrycount) from files f, tables t where f.type = 'TABLE'

AND tableid = fileid and SCHEMANAME = '<schema>'

e.g.

SELECT sum(entrycount) from files f, tables t where f.type = 'TABLE'
AND tableid = fileid and SCHEMANAME = 'SUPERU'

The system table files contains for each table the entrycount, which is exaclty the number of records.

Precondition: All file directory counters for all tables have been created sucessfully. This is the case if table Sysupdatecounterwanted is empty. The creation is implictely executed.

Regards, Christiane