Search Entire Database for String

How do I go about searching an entire DB2 database for any instance of a string criteria?

i.e. Any field in any table with the string "%Testing%"
DJmistralAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi DJmistral,

You'll have to query each table.

One way to do that is to write SQL to generate SQL.  A relatively common thing for DBAs to do.

SELECT 'SELECT * FROM ' || rtrim(table_schema) || '.' || table_name || ' WHERE ' || Column_name || ' like ''%Testing%'';'
FROM sysibm.columns
WHERE table_schema = ''
  AND table_name = ''
  AND DATA_TYPE = 'CHARACTER VARYING';

Replace the '' with the appropriate schema and table names and that QUERY will generate SELECT statements for you.  Copy and past them into the client window and execute them.


Good Luck,
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.