Find the names of last queried tables

I have a query inside my PHP program like

SELECT table_a.firstname, table_a.lastname, table_b.english_score, table_b.maths_score 
FROM table_a
INNER JOIN table_b ON table_b.student_id = table_a.id 
WHERE table_a.id = 21

I want to know the names of the tables called in this query, including joined tables. The aim is to find the schema of the affected tables which is required for further operations.

Is there a direct query to do this or will I have to use some Regex?

Answer

You can retrieve the execution plan of a query, that includes all the tables involved in a query.

For example:

create table a (b int);

create table c (d int);

explain
select a.*
from a
join c on c.d = a.b

Result:

id  select_type  table  partitions  type  pos_keys key   key_len  ref   rows  filt Extra         
--- ------------ ------ ----------- ----- -------- ----- -------- ----- ----- ---- ------------- 
1   SIMPLE       a      null        ALL   null     null  null     null  1     100  null          
1   SIMPLE       c      null        ALL   null     null  null     null  1     100  Using where.. 

Here you can see that the third column includes the names of the tables involved in the query. The execution plan comes in tabular format (default) or in JSON format. The latter has more information than this one.

You can see running example at DB Fiddle.