Find Which System Tables Have a Particular Column Name

Jim_Knicely

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 links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/SYSTEM_COLUMNS.htm

Have fun!