Calculate total row count from all tables

[debfawn]

Hello,

My boss asked me to generate a daily report (sent via email) which shows the total number of rows for all tables in our DB. Is there an easy way to calculate this sum?

Thanks all!

[pborne]

Here's a starting point. It will show the total number of rows per node and schema, adding up the number of rows in the tables. Modify as needed.

Code: Select all

SELECT node_name,
	schema_name,
	sum(row_count)
FROM (
	SELECT node_name,
		projection_schema AS schema_name,
		anchor_table_name,
		projection_name,
		row_count,
		rank() OVER (PARTITION BY node_name, projection_schema, anchor_table_name ORDER BY projection_name) AS r
	FROM projection_storage ps
	) AS d
WHERE r = 1
GROUP BY 1, 2;