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:https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/SYSTEM_COLUMNS.htm
Have fun!
About the Author
Phil Molea
Sr. Information Developer, Vertica
Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.
(Sadly, Phil passed away recently. He will be missed.)
