extracting table names given a Column name from different databases

Database
Enthusiast

extracting table names given a Column name from different databases

Hi All

SELECT DBC.TABLE_NAME FROM DBC.COLUMNS WHERE DBC.COLUMN = 'ACC_NO' AND DATABASENAME='DB1'

The above query will give me the table names from a single database called DB1, but i want the table names from a dev environment which has 1300+ databases.

How can i fetch all the table names with a single query in teradata?
And also it should not fetch the system tables.....

Can anyone help me in this regard?

Thanks
5 REPLIES
SN
Enthusiast

Re: extracting table names given a Column name from different databases

hi,

I see that you already posted this query and got the response from Robert F. Brooks.

Enthusiast

Re: extracting table names given a Column name from different databases



yes .... but am sorry that i need that query to be modified
such that it results in fetching the column names from different databases ....

Could you pls help me in this regard?

Thanks
Enthusiast

Re: extracting table names given a Column name from different databases



sorry .... Table Names not the column names ....

Thanks
Enthusiast

Re: extracting table names given a Column name from different databases

we can remove the databasename filter and keep only the columnname filter.
like
SELECT TABLENAME,DATABASENAME FROM DBC.COLUMNS DBC WHERE DBC.COLUMNname = 'ABC' ;

it should give all the databases on a teradata machine.
SN
Enthusiast

Re: extracting table names given a Column name from different databases

hi,

**Robert's solution will provide you what you want.

I have just split up his query since these are huge views.

CREATE VOLATILE TABLE DBC_VT AS (
SEL DATABASENAME FROM DBC.DATABASES
WHERE DATABASENAME NOT IN ('DBC','CRASHDUMPS')
AND DBKIND='U' ) WITH DATA ON COMMIT PRESERVE ROWS ;

SEL A.TABLENAME
FROM DBC.COLUMNS A,
DBC_VT VT
WHERE A.DATABASENAME = VT.DATABASENAME AND
A.COLUMNNAME = 'ACC_NO' ;

HTH,