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

sql database teradata
Rocketq picture Rocketq · Feb 4, 2015 · Viewed 21.5k times · Source

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

Gordon Linoff picture Gordon Linoff · Feb 4, 2015

You are looking for this:

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

Related questions

Using COLLECT STATISTICS in Teradata

In Teradata I can use a statement like ... collect statistics on my_table column(col1) This will gather stats on the table and store them in DBC views like ColumnStats, IndexStats and MultiColumnStats. I'm also under the impression that the …

Read More
Split String Teradata SQL

I'm looking to split a string in Teradata. The table might look something like this. column1 hello:goodbye:afternoon I'm trying to use SUBSTRING and INSTR to extract specific words. So, say I want to select "goodbye". I'm trying the …

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More