How to list all tables in the SAPSID schema with a count of there rows

[Former Member]

Hi

How can I list of all the tables, and a count of their rows, in the SIDADM schema. eg.

REPOSORC 123456678

COEP 564372

etc

I only have a limited knowledge of MaxDB and would appreciate if you could say which tool to use to connect to the database.

Is it possible to read this information from the statistics tables?

Thanks

Doug

Edited by: Doug Phillips on Sep 14, 2008 12:28 AM

[lbreddemann · Accepted solution]

Hi Doug,

to connect to the database you should use SQL Studio or the newer DB Studio (both available for download [here|https://www.sdn.sap.com/irj/sdn/maxdb-downloads] in SDN).

As liveCache stores it's data in objects rather than in tables, you cannot count rows there.

Anyhow, for tables it's pretty easy if you are using MaxDB 7.6 or a higher version.

To get all tables and the exact current rowcount you can use this

statement:

select t.tablename, f.entrycount
from tables t,
files f
where 
t.tableid=f.fileid
and t.owner=user

To run the statement you should be logged on as SAP<SID> / SAPR3.

In older versions of MaxDB the number of rows is not automatically available, so you would have to use the view OPTIMIZERSTATISTICS to get an estimation.

Best regards,

Lars