All Forums Database
Insaf 5 posts Joined 02/15
04 Feb 2015
How to find all the tables in Teradata with specific column names in them?

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy query?


I have seen solution for MySQL ( find-all-the-tables-in-mysql-with-specific-column- names-in-them), which won't work here because TD as far as I know don't have schemes, but instead I found  this ( retrieving-column-and-other-metadata-information-in- teradata).


And tried this code:    

WHERE column_name in ('col1', 'col2')

Obviously object `DB_NAME.COLUMNS` doesn't exist and even `DB_NAME.tables`. Any further ideas?

Insaf 5 posts Joined 02/15
04 Feb 2015

So the solution is:

SELECT TableName
WHERE DatabaseName = 'DB_NAME' and
ColumnName in ('col1', 'col2')


dnoeth 4628 posts Joined 11/04
04 Feb 2015

Please change the view to dbc.ColumnsV, dbc.Columns is an old legacy version, deprecated since TD12. Starting with TD14.10 it might return wrong results if object names longer than 30 characters are used... 


You must sign in to leave a comment.