sql script for finding tables containing a specific value


I have been given a list of 20 lookup tables (in an excel sheet) that are populated by an ETL process. Many times the mappings can be wrong and some field (or fields) of these look up table can contain the string "Data_Not_Found"

I need to create an audit sql  script that generates the following report by querying each table contained in the spread sheet

*********Tables Containing Data_Not_Found***********************
Table_Name  FieldName

Once I run this audit script I will know out of these 20 tables which tables and what specific columns of that table contain the string I am looking for

we will then fix the ETL and then keep running this audit script until all the tables are cleaned up
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
For which database?  This was posted in Oracle and SQL Server.  The solutions would probably look very different.

For only 20 tables, I would just probably hard-code the tables and columns in a script:

select 'tableA' from tablea where 'Data_Not_Found' in (col1,col2,col3);
select 'tableB' from tableb where 'Data_Not_Found' in (col1,col2,col3);
anshumaEngineeringAuthor Commented:
the tables can become 50 or 200 as well depending upon the schema. The database is teradata to be precise but here there is no subject area for that
slightwv (䄆 Netminder) Commented:
Sorry.  I cannot help with Teradata queries.

I would ask one more question that might help other Experts:
Without hard-coding, how do you plan on getting the table list from Excel to the query?
anshumaEngineeringAuthor Commented:
it doesn't matter excel or csv. we can dump the csv on a file server which can be picked by this script
slightwv (䄆 Netminder) Commented:
From some Googling...

It appears Teradata runs on Linux so the shell script should be pretty straight forward.

It looks like you can query the data dictionary to get the columns for the tables:

There are also examples out there to have SQL generate SQL to be executed:

What I'm not able to find is an example that can do everything in a single step like you can in Oracle and probably SQL Server.

Maybe there are some Teradata Experts around that might be able to offer more exact help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.