Teradata sql to give the table names when you have given the column names in a database

Database
Enthusiast

Teradata sql to give the table names when you have given the column names in a database

Hi All

Am trying to find out the Teradata sql to give the table names when you have given the column names in a database.

For Example in oracle to find the table names which uses the column EXTRACT_SYS_ID.

SELECT TABLE_NAME FROM all_tab_cols WHERE COLUMN_NAME ='EXTRACT_SYS_ID';

What is the sql related to Teradata.
7 REPLIES
Teradata Employee

Re: Teradata sql to give the table names when you have given the column names in a database

Hello,

You can use DBC.Columns table for that.

Regards,

Adeel
Enthusiast

Re: Teradata sql to give the table names when you have given the column names in a database

Hi

select * from DBC.Columns where ColumnName = 'ACC_NO'

The above query results with the System tables also ....
but i need only the user tables .......

Can u pls suggest me the way to do it.

Thanks
Dileep
Teradata Employee

Re: Teradata sql to give the table names when you have given the column names in a database

Well...for that you can filter using DatabaseName...

Regards,

Adeel
Enthusiast

Re: Teradata sql to give the table names when you have given the column names in a database



is it this way ??

select DatabaseName.Table_Name from DBC.Columns where Column_Name = 'ACC_NO' ??

Could you please give me the exact query??

Regards

Teradata Employee

Re: Teradata sql to give the table names when you have given the column names in a database

Can you please define the system tables?

And do you know the name of database in which you need to search for the column?

Regards,

Adeel
Enthusiast

Re: Teradata sql to give the table names when you have given the column names in a database



yah .... say for example Database name is Account.
but still i doubt whether Account.Table_Name works fine ??

Regards
Teradata Employee

Re: Teradata sql to give the table names when you have given the column names in a database

You can use it as follows:

SELECT * FROM DBC.Columns WHERE ColumnName = 'ACC_NO' AND DatabaseName = 'Account';

Regards,

Adeel