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