Find empty tables in Redshift

This query returns a list of all tables with no rows.

Query

select tab.table_schema,
       tab.table_name
from svv_tables tab
left join svv_table_info tinf 
          on tab.table_schema = tinf.schema 
          and tab.table_name = tinf.table
where tinf.tbl_rows = 0 or tinf.tbl_rows is null
      and tab.table_type = 'BASE TABLE'
      and tab.table_schema not in('pg_catalog','information_schema')

Columns

  • table_schema - table schema name
  • table_name - table name

Rows

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

Sample results

Empty tables in our tickit repository:

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