How to find all the tables in Teradata with specific column names in them?

Database
Enthusiast

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?


And tried this code:    

SELECT*
FROM DB_NAME.COLUMNS
WHERE column_name in ('col1', 'col2')

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


2 REPLIES
Enthusiast

Re: How to find all the tables in Teradata with specific column names in them?

So the solution is:

SELECT TableName
FROM DBC.COLUMNS
WHERE DatabaseName = 'DB_NAME' and
ColumnName in ('col1', 'col2')
Junior Contributor

Re: How to find all the tables in Teradata with specific column names in them?

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