|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/22/2008 11:30:56 AM
Posts: 22,
Visits: 41
|
|
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
Dileep
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76,
Visits: 147
|
|
hi,
I see that you already posted this query and got the response from Robert F. Brooks.
-SN
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/22/2008 11:30:56 AM
Posts: 22,
Visits: 41
|
|
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
Dileep
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/22/2008 11:30:56 AM
Posts: 22,
Visits: 41
|
|
sorry .... Table Names not the column names ....
Thanks
Dileep
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/3/2008 5:26:33 AM
Posts: 8,
Visits: 64
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76,
Visits: 147
|
|
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,
-SN
|
|
|
|