Find Which Tables Have a Particular Column Name: Quick Tip

Your Vertica database probably contains hundreds, if not thousands, of tables. Trying to remember which tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.COLUMNS system table!

Example:

Which tables have a column named “DateKey”?

dbadmin=> SELECT table_schema, table_name, column_name
dbadmin-> FROM v_catalog.columns
dbadmin-> WHERE column_name = 'DateKey'
dbadmin-> ORDER BY 1, 2, 3;
table_schema | table_name | column_name
--------------+-------------------------+-------------
warehouse | BridgeCustomerAccount | DateKey
warehouse | BridgeHouseholdCustomer | DateKey
warehouse | DimDate | DateKey
warehouse | FactAccount | DateKey
warehouse | FactAccountScore | DateKey
warehouse | FactCustomer | DateKey
warehouse | FactCustomerScore | DateKey
warehouse | FactHousehold | DateKey
warehouse | FactHouseholdScore | DateKey
(9 rows)

Which tables have a column with a name that contains the text “ssn”?

dbadmin=> SELECT table_schema, table_name, column_name
dbadmin-> FROM v_catalog.columns
dbadmin-> WHERE column_name ILIKE '%ssn%';
table_schema | table_name | column_name
--------------+---------------+-------------
public | employee_fact | ssn
dw | emp_dim | SSN
(2 rows)

Have fun!