ColumnValueList

Database

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

Tags (1)
1 REPLY
Teradata Employee

Re: ColumnValueList

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