Find Which Tables Have a Particular Column Name: Quick Tip

Jim Knicely authored this 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) Helpful link:

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

Have fun!