Number of records per table (IBC Vertica)

Hello Celonis community,

I want to determine the number of data records in vertica for all tables of a schema (SAP System).

The easiest way should be via the system table “tables”

so I get all tables of a SAP connection, which also works
select * from tables where table_schema = ‘XYZ’

but unfortunately the following does not work:
select t.table_name,
(select count(1) from t.table_name) as table_row_count
from tables as t where t.table_schema = 'XYZ

-> Execution error: Not allowed to access schema t

the other way via a system table does not work reliably:
SELECT table_schema,
table_name,
SUM(row_count) AS row_count
FROM (SELECT table_schema,table_name,
NVL(CASE
WHEN NOT is_segmented THEN (row_count / node_cnt)::INT
ELSE row_count
END, 0) row_count
FROM (SELECT t.table_schema,
t.table_name,
ps.projection_name,
(SELECT MIN(is_segmented)
FROM projections p
WHERE p.projection_name = ps.projection_name
AND p.projection_schema = ps.anchor_table_schema) is_segmented,
node_cnt,
SUM(ps.row_count - NVL(dv.deleted_row_count, 0)) row_count
FROM tables t
LEFT JOIN projection_storage ps
ON t.table_id = ps.anchor_table_id
LEFT JOIN delete_vectors dv
ON dv.node_name = ps.node_name
AND dv.schema_name = ps.anchor_table_schema
AND dv.projection_name = ps.projection_name
CROSS JOIN (SELECT COUNT(1) node_cnt FROM nodes WHERE node_type = ‘PERMANENT’) n
where
t.table_schema = 'XYZ’
GROUP BY 1, 2, 3, 4, 5
LIMIT 1 OVER(PARTITION BY t.table_schema, t.table_name ORDER BY 1)) foo) foo2
GROUP BY table_schema,table_name
ORDER BY table_schema,table_name

Anybody got any ideas?
Br
Marcus

Hi Marcus,

I am afraid that the only way to retrieve the number of records for a table is a simple
SELECT COUNT(1) FROM TABLE_NAME

There is no easy way to retrieve this information for all tables in a schema.

Best regards,
Leonid