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

Question

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, which won't work here because TD as far as I know don't have schemes, but instead I found this.

And tried this code:

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

But surely subquery must be used to get TableName, because DBC.COLUMNS doesn't have that field. Any further ideas?

Answer 1

You are looking for this:

SELECT tablename
FROM dbc.columnsV
WHERE ColumnName in ('col1', 'col2')