sql - Search all columns in Informix table for a value -

i new informix, remember doing sql server. want query columns in given table specified value.

everything googled references doing in sql server.

ideas?

there isn't built-in way it. you'd have do:

select * table column1 = <your-value> union select * table column2 = <your-value> union ... 

---

automatic query generation

is there programmatic way generate mass union-select statements? of target tables have numerous columns.

what's weapon of choice? database name, table name, , value? weapon of choice sqlcmd, program available iiug software archive , not microsoft's johnny-come-lately creation of same name.

dbname=stores table=customers value=raymond  sqlcmd -d'\n' -d $dbname -e \     "select 'select * $table ', c.colname,             '::varchar(64) = ''$value''', 'union'        informix.syscolumns c        join informix.systables t on t.tabid = c.tabid       t.tabname = '$table' order colno" | sed '$d' 

the output query keywords in upper-case; meta-query generates in lower-case. meta-query joining systables syscolumns. "-d '\n'" option says "the field delimiter newline" (as record delimiter). make sure union on line of own make easy remove last one. cast columns varchar(64) can compared string regardless of source type - regardless of source type, because byte, text, blob , clob columns won't convert. if want like '%raymond%' predicate, adapt query accordingly.

you can achieve similar results perl , dbi , dbd::informix.

getting output db-access messy; you'd use built-in 'output "/dev/stdout" without headings select ...' ... remainder of query shown above. have rid of last 2 lines of output, harder last one. simplest brute force - run 2 copies of sed '$d' on output, solution not scale well. failing that, save output in file , used ed or ex edit it.

example output:

select * customer customer_num ::varchar(64) = 'raymond' union select * customer fname ::varchar(64) = 'raymond' union select * customer lname ::varchar(64) = 'raymond' union select * customer company ::varchar(64) = 'raymond' union select * customer address1 ::varchar(64) = 'raymond' union select * customer address2 ::varchar(64) = 'raymond' union select * customer city ::varchar(64) = 'raymond' union select * customer state ::varchar(64) = 'raymond' union select * customer zipcode ::varchar(64) = 'raymond' union select * customer phone ::varchar(64) = 'raymond' 

when output above fed second copy of sqlcmd, produced output:

 105|raymond|vector|los altos sports|1899 la loma drive||los altos|ca|94022|415-776-3249