Find Which System Tables Have a Particular Column Name: Quick Tip

Posted October 10, 2018 by Phil Molea, Sr. Information Developer, Vertica

Jim Knicely authored this tip.

There are over 180 built-in Vertica system tables in the V_CATALOG and V_MONITOR schemas. Trying to remember which system tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.SYSTEM_COLUMNS system table!

Example: dbadmin=> SELECT table_schema, table_name, column_name dbadmin-> FROM v_catalog.system_columns dbadmin-> WHERE column_name = 'anchor_table_name' dbadmin-> ORDER BY 1, 2, 3; table_schema | table_name | column_name --------------+-----------------------------+------------------- v_catalog | projections | anchor_table_name v_monitor | column_storage | anchor_table_name v_monitor | deployment_projections | anchor_table_name v_monitor | projection_refreshes | anchor_table_name v_monitor | projection_storage | anchor_table_name v_monitor | projection_usage | anchor_table_name v_monitor | rebalance_projection_status | anchor_table_name (7 rows) Helpful link:

Have fun!