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

Confused about your Teradata database?

You don't have to be. There's an easy way to understand the data in your databases.

I want to understand

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