Find Which Tables Have a Particular Column Name

Jim_Knicely

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)

Helpful link:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/COLUMNS.htm

Have fun!