Gregory Machin <[EMAIL PROTECTED]> wrote on 07/29/2005 08:00:07 AM: > Hi > 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 > [EMAIL PROTECTED] > [EMAIL PROTECTED] > www.linuxpro.co.za > 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 data. 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
