Enumerating tables used in mysql query?

Question

Is there any way to enumerate tables used in mysql query?

Lets say I have query :

SELECT * FROM   db_people.people_facts pf
INNER JOIN db_system.connections sm ON sm.source_id = pf.object_id
INNER JOIN db_people.people p ON sm.target_id = p.object_id
ORDER BY pf.object_id DESC

And i want in return array:

$tables = array(
[0] => 'db_people.people_facts',
[1] => 'db_system.connections',
[2] => 'db_people.people',
);

PS. Thanks everyone for answers :)

Answer 1

Depending on what you're using it for, MySQL's EXPLAIN could do the trick for you:

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Answer 2

Yes, you can get information about tables and columns that are part of a query result. This is called result set metadata.

The only PHP solution for MySQL result set metadata is to use the MySQLi extension and the mysqli_stmt::result_metadata() function.

$stmt = $mysqli->prepare("SELECT * FROM   db_people.people_facts pf
  INNER JOIN db_system.connections sm ON sm.source_id = pf.object_id
  INNER JOIN db_people.people p ON sm.target_id = p.object_id
  ORDER BY pf.object_id DESC");

$meta = $stmt->result_metadata();

$field1 = $meta->fetch_field();

echo "Table for field " . $field1->name . " is " . $field1->table . "\n";

You'll have to build the array of distinct tables used in the query yourself, by looping over the fields.

Answer 3

The solution marked as good will return only the result tables. But if you do the next query it will fail:

SELECT users.* FROM users, cats, dogs WHERE users.id = cats.user_id

Will return only users and not cats and dogs tables.

The best solution is find a good parser, another solution is using REGEX and EXPLAIN query (more info in the next link):

http://stackoverflow.com/questions/3397568/get-mysql-tables-in-a-query

But I think that another good solution is list all tables and search them inside the query, you can cache the list of tables.

EDIT: When searching for tables, better use a preg like:

// (`|'|"| )table_name(\1|$)
if(preg_match('/(`|\'|"| )table_name(\1|$)/i', $query))
    // found

If not, it can return false positives with for example "table_name2", "table_name3"... table_name will return FOUND two times.