List tables by the number of rows in Vertica

Bart Gawrych - Dataedo Team Bart Gawrych 2019-09-17

Table of Contents:


    Query below returns list of tables in with their number of rows.

    Query

    with num_rows as (
        select schema_name,
               anchor_table_name as table_name,
               sum(total_row_count) as rows
        from v_monitor.storage_containers sc
        join v_catalog.projections p
             on sc.projection_id = p.projection_id
             and p.is_super_projection = true
        group by schema_name,
                 table_name,
                 sc.projection_id
    )
    select schema_name,
           table_name,
           max(rows) as rows
    from num_rows
    group by schema_name,
             table_name
    order by rows desc;
    

    Columns

    • schema_name - table name with schema name
    • table_name - table name with schema name
    • rows - number of rows in a table

    Rows

    • One row represents one table
    • Scope of rows: all tables in a database excluding tables without rows
    • Ordered by rows in table descending

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept