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….

October 9, 2018•1 minute read

Shot of a young computer programmer working on source code

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!

Share this post

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

Follow

More from the author

Manutan combines digital services with the human touch to delight customers

Customer Experience

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

January 31, 2024 • 4 minute read

Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

January 18, 2024 • 4 minute read

SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

November 21, 2023 • 3 minute read

.wp-block-group{box-sizing:border-box;}.is-small-text{font-size:.875em;}.is-regular-text{font-size:1em;}.is-large-text{font-size:2.25em;}.is-larger-text{font-size:3em;}.has-drop-cap:not(:focus):first-letter{float:left;font-size:8.4em;font-style:normal;font-weight:100;line-height:.68;margin:.05em .1em 0 0;text-transform:uppercase;}body.rtl .has-drop-cap:not(:focus):first-letter{float:none;margin-left:.1em;}p.has-drop-cap.has-background{overflow:hidden;}p.has-background{padding:1.25em 2.375em;}:where(p.has-text-color:not(.has-link-color)) a{color:inherit;}p.has-text-align-left[style*="writing-mode:vertical-lr"],p.has-text-align-right[style*="writing-mode:vertical-rl"]{rotate:180deg;}h1.has-background,h2.has-background,h3.has-background,h4.has-background,h5.has-background,h6.has-background{padding:1.25em 2.375em;}h1.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h1.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]),h2.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h2.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]),h3.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h3.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]),h4.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h4.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]),h5.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h5.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]),h6.has-text-align-left[style*=writing-mode]:where([style*=vertical-lr]),h6.has-text-align-right[style*=writing-mode]:where([style*=vertical-rl]){rotate:180deg;}:root{--wp-admin-theme-color:#007cba;--wp-admin-theme-color--rgb:0,124,186;--wp-admin-theme-color-darker-10:#006ba1;--wp-admin-theme-color-darker-10--rgb:0,107,161;--wp-admin-theme-color-darker-20:#005a87;--wp-admin-theme-color-darker-20--rgb:0,90,135;--wp-admin-border-width-focus:2px;--wp-block-synced-color:#7a00df;--wp-block-synced-color--rgb:122,0,223;--wp-bound-block-color:#9747ff;}@media (min-resolution:192dpi){:root{--wp-admin-border-width-focus:1.5px;}}.wp-element-button{cursor:pointer;}:root{--wp--preset--font-size--normal:16px;--wp--preset--font-size--huge:42px;}:root .has-very-light-gray-background-color{background-color:#eee;}:root .has-very-dark-gray-background-color{background-color:#313131;}:root .has-very-light-gray-color{color:#eee;}:root .has-very-dark-gray-color{color:#313131;}:root .has-vivid-green-cyan-to-vivid-cyan-blue-gradient-background{background:linear-gradient(135deg,#00d084,#0693e3);}:root .has-purple-crush-gradient-background{background:linear-gradient(135deg,#34e2e4,#4721fb 50%,#ab1dfe);}:root .has-hazy-dawn-gradient-background{background:linear-gradient(135deg,#faaca8,#dad0ec);}:root .has-subdued-olive-gradient-background{background:linear-gradient(135deg,#fafae1,#67a671);}:root .has-atomic-cream-gradient-background{background:linear-gradient(135deg,#fdd79a,#004a59);}:root .has-nightshade-gradient-background{background:linear-gradient(135deg,#330968,#31cdcf);}:root .has-midnight-gradient-background{background:linear-gradient(135deg,#020381,#2874fc);}.has-regular-font-size{font-size:1em;}.has-larger-font-size{font-size:2.625em;}.has-normal-font-size{font-size:var(--wp--preset--font-size--normal);}.has-huge-font-size{font-size:var(--wp--preset--font-size--huge);}.has-text-align-center{text-align:center;}.has-text-align-left{text-align:left;}.has-text-align-right{text-align:right;}#end-resizable-editor-section{display:none;}.aligncenter{clear:both;}.items-justified-left{justify-content:flex-start;}.items-justified-center{justify-content:center;}.items-justified-right{justify-content:flex-end;}.items-justified-space-between{justify-content:space-between;}.screen-reader-text{border:0;clip:rect(1px,1px,1px,1px);-webkit-clip-path:inset(50%);clip-path:inset(50%);height:1px;margin:-1px;overflow:hidden;padding:0;position:absolute;width:1px;word-wrap:normal !important;}.screen-reader-text:focus{background-color:#ddd;clip:auto !important;-webkit-clip-path:none;clip-path:none;color:#444;display:block;font-size:1em;height:auto;left:5px;line-height:normal;padding:15px 23px 14px;text-decoration:none;top:5px;width:auto;z-index:100000;}html :where(.has-border-color){border-style:solid;}html :where([style*=border-top-color]){border-top-style:solid;}html :where([style*=border-right-color]){border-right-style:solid;}html :where([style*=border-bottom-color]){border-bottom-style:solid;}html :where([style*=border-left-color]){border-left-style:solid;}html :where([style*=border-width]){border-style:solid;}html :where([style*=border-top-width]){border-top-style:solid;}html :where([style*=border-right-width]){border-right-style:solid;}html :where([style*=border-bottom-width]){border-bottom-style:solid;}html :where([style*=border-left-width]){border-left-style:solid;}html :where(img[class*=wp-image-]){height:auto;max-width:100%;}:where(figure){margin:0 0 1em;}html :where(.is-position-sticky){--wp-admin--admin-bar--position-offset:var(--wp-admin--admin-bar--height,0px);}@media screen and (max-width:600px){html :where(.is-position-sticky){--wp-admin--admin-bar--position-offset:0px;}}.wp-container-core-post-content-is-layout-1.wp-container-core-post-content-is-layout-1 > :where(:not(.alignleft):not(.alignright):not(.alignfull)){margin-left:0 !important;}.wp-container-1{top:calc(24px + var(--wp-admin--admin-bar--position-offset,0px));position:sticky;z-index:10;}.wp-elements-48875b53c758df36ef0c32648dc63b67 a:where(:not(.wp-element-button)){color:var(--wp--preset--color--central-palette-4);}.skip-link.screen-reader-text{border:0;clip:rect(1px,1px,1px,1px);clip-path:inset(50%);height:1px;margin:-1px;overflow:hidden;padding:0;position:absolute !important;width:1px;word-wrap:normal !important;}.skip-link.screen-reader-text:focus{background-color:#eee;clip:auto !important;clip-path:none;color:#444;display:block;font-size:1em;height:auto;left:5px;line-height:normal;padding:15px 23px 14px;text-decoration:none;top:5px;width:auto;z-index:100000;}