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...
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.