Get largest tables in database

reli

I run this script to get the largest table:

SELECT v.table_schema as table_schema, t.table_name AS table_name,
SUM(ps.wos_row_count + ps.ros_row_count) AS row_count,
SUM(ps.wos_used_bytes + ps.ros_used_bytes) AS byte_count
FROM tables t
inner JOIN projections p ON t.table_id = p.anchor_table_id
inner JOIN projection_storage ps on p.projection_name = ps.projection_name
inner join v_catalog.tables v on v.table_name = t.table_name and v.table_schema = t.table_schema
and
WHERE (ps.wos_used_bytes + ps.ros_used_bytes) > 500000
GROUP BY t.table_name,v.table_schema
ORDER BY SUM(ps.wos_used_bytes + ps.ros_used_bytes) DESC

I get in the result same table with the same size row_count is equal, if they have the same name but different scheme
like schemeA.tableA , schemeB.tableB, when tableB is** empty**,
what I do wrong in the script?

Jim_Knicely

Maybe this is a simpler solution?

SELECT schema_name, anchortablename AS anchor_table_name, MAX(row_count) row_count, MAX(size) size FROM (SELECT schema_name, projection_name, p.anchortablename, SUM(total_row_count) row_count, SUM(used_bytes) size FROM storage_containers s JOIN vs_projections p ON s.schema_name = p.schemaname AND s.projection_name = p.name GROUP BY 1, 2, 3 HAVING SUM(used_bytes) > 500000) foo GROUP BY 1, 2 ORDER BY 3 DESC;

reli

Thank you so much it's great!

Sergey_Cherepan_1

Oddly enough, Vertical Management Console do not provide answer to this very common question.