All Forums Database
shrikrishna 3 posts Joined 12/05
24 Mar 2006
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...ThanksShrikrishna

24 Mar 2006

try thisselect a.*from dbc.columns a, dbc.tables bwhere a.databasename = b.databasenameand b.tablekind = 'V'and a.columnname like '%XYZ%'this will give all the rows from dbc.columns (databasename, tablename, colname ........)

Barry-1604 176 posts Joined 07/05
24 Mar 2006

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 bwhere a.databasename = b.databasenameand 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

shrikrishna 3 posts Joined 12/05
25 Mar 2006

Thank you very much!!!

You must sign in to leave a comment.