Gregory Machin <gregory.machin@stripped> wrote on 07/29/2005 08:00:07 AM:
> Please could advise me.
> I need search all the tables in a database for a single string. I'm
> trying to figure out how, where and what other tables exponent cms
> saves it text pages and references to, so i can finish writing a mass
> page import module....
> In short can mysql do a recursive search, through all the table's in
> a database ..
> Many Thanks ..
> Gregory Machin
> Web Hosting Solutions
> Scalable Linux Solutions
> www.iberry.info (support and admin)
> www.goeducation (support and admin)
> +27 72 524 8096
Recursive search? No, not a native MySQL function. To do it through MySQL
you would need to write a script that provides all of the SQL statements
you would need to search every column of every table that may hold your
target data (your piece of "special text").
However!! if your tables are MyISAM, you could turn off the server and
scan the actual data files for your target string. It will not tell you in
which column your target data is in but it will tell you which tables
contain that piece of data. That will give you only a few tables to look
at. With those file names in hand (filename = table name) you can look up
the definitions of each table (SHOW CREATE TABLE tablename\G) and narrow
your search to just the columns capable of holding your piece of test
It's the brute-force approach, I know, but it should be relatively quick
and will help you to quickly narrow your search. This might also work if
you have InnoDB tables and are creating a separate tablespace for each
file. If you are using the default InnoDB behavior of one shared
tablespace for all files, this technique would not help.
Unimin Corporation - Spruce Pine