I've around 150 different databases, with a large number of tables each on a single of my servers. I'm searching to determine what database consists of a particular person's title. At this time, i am using phpmyadmin to look each database indvidually, however i would love to have the ability to search all databases and all sorts of tables at the same time. Is possible? Wouldso would Time passes about carrying this out?
An answer is always to make use of the
information_schema database, to list out all database, all tables, all fields, and loop total that...
There's this script that may help not less than some area of the work : anywhereindb (estimating) :
This code is search all of the tables and all of the rows and posts inside a MYSQL Database. The code is designed in PHP. For faster result, we're only searching within the varchar area.
But, as Harmen noted, this is only for one database -- meaning you'd need to wrap something arround it, to loop over each database in your server.
Here's another solution, with different saved procedure -- meaning less client/server calls, that might allow it to be faster : http://kedar.nitty-witty.com/miscpages/mysql-search-through-all-database-tables-columns-stored-procedure.php
The proper way to do it is always to NORMALIZE your computer data to begin with!!!
You say title - but almost everyone has a minimum of 2 names (a surname along with a forename) are these separate or perhaps in exactly the same area? If they're within the same area, then what order will they come in? how could they be capitalized?
The best way to try and identify in which the data may be is always to write a course in C which sifts the raw documents (as the DBMS is shut lower) searching for the information - but which will only let you know what table they apppear in.
Failing you need to write some PHP which iterates through each database ('SHOW databases' works similar to a choose statement), then iterates through each table within the database, then creates a Choose statement blocking on each CHAR or VARCHAR column big enough to carry the title you're searching for (try running 'DESC $table').
Best of luck.
The very best answer most likely is dependent how frequently for you to do this. If it's ad-hoc once per week type stuff then your above solutions are great.
If you wish to do that type of search when a second, maybe produce a "data warehouse" database that consists of only the table:posts you need to search (heavily indexed, having a reference to the origin database if that's needed) populated by cron job or by saved methods driven by alterations in the 150 databases...