[SOLVED] How to search in all tables ?

[Panjabel]

Hello

i have more than 10000 tables in database.

Is there any way to search in all tables ?

Thank you

[atomicrabbit]

Depends what need need to query. You can do something like this:

SELECT DISTINCT ItemName
FROM Sale, Department
WHERE Sale.DepartmentName = Department.DepartmentName
      AND Department.DepartmentFloor = 2;

 

where the tables look like this:

CREATE TABLE Department (
  DepartmentName VARCHAR(10) NOT NULL,
  DepartmentFloor SMALLINT UNSIGNED NOT NULL,
  DepartmentPhone SMALLINT UNSIGNED NOT NULL,
  EmployeeNumber SMALLINT UNSIGNED NOT NULL REFERENCES Employee,
  PRIMARY KEY (DepartmentName));

CREATE TABLE Sale (
  SaleNumber INTEGER UNSIGNED NOT NULL,
  SaleQuantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  ItemName VARCHAR(30) NOT NULL REFERENCES Item,
  DepartmentName VARCHAR(10) NOT NULL REFERENCES Department,
  PRIMARY KEY (SaleNumber));

 

Does each table in your db have a similar column name. I'm not too sure how the multiple table queries work, but maybe you can use a wildcard for the table name. Have you tried that?? You need to give more info about what you're doing before anyone can help.

 

You can also run a loop through each of the tables and store the data into a single array. So for each loop, you would be querying a different table, do whatever you need to do with it. But this also depends on what your table names are and ESPECIALLY what you are querying. Check this pseudo-code out (this is not real code, just a concept):

 

for ($i = 1; $i <= 1000; $i ++) {
   $result = mysql_query("SELECT column_name FROM table_name_" . i . " WHERE whatever = something")
   // do whatever you need to do with the data
}

[atomicrabbit]

You can also try using the SHOW TABLE STATUS query to display the entire contents of your database. This way, you can load all your table names into a separate array and THEN you can go through the loop above using the table names that were stored into the array. See this page for more info on the SHOW TABLE STATUS query

[Panjabel]

Thank you very much Guys,

I will try your ways,

God bless you !!!