• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3509
  • Last Modified:

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%"
1
DJmistral
Asked:
DJmistral
1 Solution
 
Kent OlsenData 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now