URGENT:how to find col names and its values matching to my condition

Database

URGENT:how to find col names and its values matching to my condition

Hi,
We have 500+ views and i have to look for particular column name whcih contains string 'XYZ'.Column name may be like 'XYZ_CUST' or 'CUST_XYZ_CD' or anything containing 'XYZ' string.
Also its possible to find particular value in that column? value like '1234'
under that particular column.
Any help? Plz...

Thanks
Shrikrishna
3 REPLIES
Enthusiast

Re: URGENT:how to find col names and its values matching to my condition

try this

select a.*
from dbc.columns a, dbc.tables b
where a.databasename = b.databasename
and b.tablekind = 'V'
and a.columnname like '%XYZ%'

this will give all the rows from dbc.columns (databasename, tablename, colname ........)

Enthusiast

Re: URGENT:how to find col names and its values matching to my condition

To expand on the last reply, you can then format the results into a SELECT statement and execute all of those:

select 'SELECT COUNT(*) FROM ' || b.databasename || '.' ||
b.tablename || ' WHERE ' || a.columnname || ' LIKE ''%1234%'';'
from dbc.columns a, dbc.tables b
where a.databasename = b.databasename
and b.tablekind = 'V'
and a.columnname like '%XYZ%';

When you execute the select above, it will format a SELECT statement for every column name in a view that has "XYZ" in it. Then, you can cut/paste the SELECT statements into BTEQ or SQL Assistant to find out where the column contains '1234'. You can also use BTEQ export to have the statements written to a file and then execute them from the file.

Good luck.

Barry

Re: URGENT:how to find col names and its values matching to my condition

Thank you very much!!!