Query below lists all projections and their table in Vertica database.

Query

select projection_schema,
       projection_name,
       anchor_table_name as table_name,
       case when is_up_to_date then 'YES'
            else 'NO' end as is_up_to_date,
       case when is_prejoin then 'YES'
            else 'NO' end as is_prejoin,
       case when is_super_projection then 'YES'
            else 'NO' end as is_super_projection,
       case when is_aggregate_projection then 'YES'
            else 'NO' end as is_aggregate_projection
from v_catalog.projections
order by projection_schema,
         projection_name;

Columns

• projection_schema - schema name of hte projection
• projection_name -projection name
• table_name - table name on which projection was created ( FROM clause)
• is_up_to_date - indicate if data in projection is up to date
• is_prejoin -indicate if projection is prejoin projection
• is_super_projection - indicate if projection is super projection (contains all columns from anchor table)
• is_aggregate_projection - indicate if projection is aggregate projection (contains one of aggregate function)

Rows

• One row represents one projection in the database
• Scope of rows: all projections in the database
• Ordered by schema and projection name

Sample results