All Forums Database
AROLD001 3 posts Joined 12/15
03 Jun 2016
ColumnValueList

 
Hi Teradata Experts,
I generally use simple DBC.COLUMNS to go ahead and get various columnname, tablename, databasename for scanning tables that have same columns. And then manually write basic queries for each table to go ahead and find if particular string exists in those shortlisted tables.
eg1:
SEL * FROM DBC.COLUMNS WHERE columnname (NOT CS) LIKE ('%SRC_SYS_NM%') AND databasename (NOT CS) LIKE ('%PD_PTY_VMDB%') ORDER BY TABLENAME;
 
eg2:
SEL DISTINCT src_sys_nm FROM PD_PTY_VMDB.acct WHERE src_sys_nm = 'LMS';
SEL DISTINCT src_sys_nm FROM PD_PTY_VMDB.acct_chld WHERE src_sys_nm = 'LMS';
... (about 150 tables)
 
So is there a way to replace the laborious effort of writing multiple queries in eg 2 by means of one query at DBC user level so that searching for string across multiple databases may be tackled effortlessly.
 
Cheers,
DA
 

tomnolan 594 posts Joined 01/08
03 Jun 2016

You could write a script or a program to do that.

You must sign in to leave a comment.