We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Find Which System Tables Have a Particular Column Name — Vertica Forum

Find Which System Tables Have a Particular Column Name

Jim_KnicelyJim_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!

Sign In or Register to comment.