Find empty tables in Vertica database

Query below returns tables in a database without any rows (empty tables).

Query

with num_rows as (
    select sc.schema_name,
           p.anchor_table_name as table_name,
           sum(sc.total_row_count - sc.deleted_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
) 
select t.table_schema,
       t.table_name
from v_catalog.tables t
left join num_rows nr
          on nr.schema_name = t.table_schema
          and nr.table_name = t.table_name
where nr.rows is null or nr.rows < 1
group by t.table_schema,
         t.table_name
order by table_schema,
         table_name;

Columns

  • table_schema - schema name
  • table_name - table name

Rows

  • One row represents one empty table
  • Scope of rows: all empty tables (without rows)
  • Ordered by table schema name and table name

Sample results

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