Find Which System Tables Have a Particular Column Name
Jim_Knicely
- Select Field - Administrator
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!
0