How to search for a string in the whole database?

Question

I have an informix database consisting of a large number of tables.

I know that there is a string "example" somewhere inside some table, but don't know which table it is or which column it is. (I know this is a very rare case)

Because of the large number of tables, there is no way to look for it manually. How do i find this value inside this large database? Is there any query to find it?

Thanks in advance!

Answer 1

Generally, you have two approaches.

One way would be to dump each table to individual text files and grep the files. This can be done manually on a small database, or via a script in the case of a larger one. Look into UNLOAD and dbaccess. Example in a BASH script: (you'll need to generate the table list in the script either statically or via a query.)

unload_tables () {

for table in ${TABLE_LIST} do
    dbaccess database_name << EOF
    unload to "${OUT_PATH}/${table}/.out"
    select * from $table;
    EOF
done
}

Or, this is a little more complicated. You can create a specific SELECT (filtering each column by "example") for each table and column in your db in a similar automated fashion using systables and syscolumns, then run each sql.

For example, this query shows you all columns in all tables:

SELECT tabname, colno, colname, coltype, collength
FROM systables a, syscolumns b 
WHERE a.tabid = b.tabid

It is easy to adapt this such that the SELECT return a proper formatted SQL string that allows you to query the database for matches to "example". Sorry, I don't have a full solution at the ready, but if you google for "informix systables syscolumns", you will see lots of ways this information can be used.

Answer 2

In Informix, determining the columns that contain character data is fiddly, but doable. Fortunately, you're unlikely to be using the esoteric features such as DISTINCT TYPE that would make the search harder. (Alphadogg's suggestion of unload - using dbexport as I note in my comment to his answer - makes sense, especially if the text might appear in a CLOB or TEXT field.) You need to know that types CHAR, NCHAR, VARCHAR, NCARCHAR, and LVARCHAR have type numbers of 0, 13, 15, 16 and 43. Further, if the column is NOT NULL, 256 is added to the number. Hence, the character data columns in the database are found via this query:

SELECT t.owner, t.tabname, c.colname, t.tabid, c.colno
    FROM "informix".systables t, "informix".syscolumns c
    WHERE t.tabid = c.tabid
      AND c.coltype IN (0, 13, 15, 16, 43, 256, 269, 271, 272, 299)
      AND t.tabtype = 'T'  -- exclude views, synonyms, etc.
      AND t.tabid  >= 100  -- exclude the system catalog
    ORDER BY t.owner, t.tabname, c.colno;

This generates the list of places to search. You could get fancy and have the SELECT generate a string suitable for resubmission as a query - that's left as an exercise for the reader.