Search all columns in Informix table for a value

I am new to Informix, but remember doing this with SQL Server. Basically I want to query all columns in a given table for a specified value.

Everything I googled references doing i开发者_StackOverflow中文版t in SQL server.

Ideas?

There isn't a built-in way to do it. You'd have to do:

SELECT * FROM Table WHERE Column1 = <your-value>
UNION
SELECT * FROM Table WHERE Column2 = <your-value>
UNION
...

Automatic query generation

Is there a programmatic way to generate the mass UNION-SELECT statements? Some of my target tables have numerous columns.

What's your weapon of choice? What is that database name, the table name, and the value? My weapon of choice is SQLCMD, the program is available from the IIUG Software Archive and not Microsoft's johnny-come-lately creation of the same name.

dbname=stores
table=customers
value=Raymond

sqlcmd -D'\n' -d $dbname -e \
    "select 'SELECT * FROM $table WHERE ', c.colname,
            '::VARCHAR(64) = ''$value''', 'UNION'
       from informix.syscolumns as c
       join informix.systables as t on t.tabid = c.tabid
      where t.tabname = '$table' order by colno" |
sed '$d'

The output query keywords are in upper-case; the meta-query that generates it is in lower-case. The meta-query is a joining SysTables with SysColumns. The "-D '\n'" option says "the field delimiter is a newline" (as well as the record delimiter). I make sure the UNION is on a line of its own to make it easy to remove the last one. I cast the columns to VARCHAR(64) so that they can be compared with the string regardless of source type - almost regardless of source type, because BYTE, TEXT, BLOB and CLOB columns won't convert. If you want a LIKE '%Raymond%' predicate, adapt the query accordingly.

You can achieve similar results with Perl and DBI and DBD::Informix.

Getting the output with DB-Access would be messy; you'd probably use the built-in 'OUTPUT TO "/dev/stdout" WITHOUT HEADINGS select ...' where the ... is the remainder of the query shown above. You then have to get rid of the last two lines of the output, which is much harder than just the last one. Probably the simplest is brute force - run two copies of sed '$d' on the output, but the solution does not scale well. Failing that, save the output in a file and used ed or ex to edit it.

Example output:

SELECT * FROM customer WHERE
customer_num
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
fname
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
lname
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
company
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
address1
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
address2
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
city
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
state
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
zipcode
::VARCHAR(64) = 'Raymond'
UNION
SELECT * FROM customer WHERE
phone
::VARCHAR(64) = 'Raymond'

When the output above is fed into a second copy of SQLCMD, it produced the output:

 105|Raymond|Vector|Los Altos Sports|1899 La Loma Drive||Los Altos|CA|94022|415-776-3249

If you want to get what tables has determined column do that >>

select t.tabname from systables t where t.tabid in ( 
    select tabid from syscolumns c where c.colname = 'NAME_OF_THE_COLUMN')

[]s Alf