I need to extract all the accessed tables from a SQL statement (with different type of complexity) but i need to know from which token this table came from, for an example, if a have this query:
CREATE TABLE TABLE_B AS B SELECT * FROM (SELECT * FROM (SELECT * FROM table_A, table_B WHERE (SELECT * FROM table_X LEFT JOIN TABLE_C ON atrb))) WHERE (SELECT * FROM table_X LEFT JOIN TABLE.C ON atrb)
The expected output to my scenario would be:
['TABLE_B', 'TABLE_A']
Because i don't want the tables accessed in where statement.
So i think a good alternative to it, would be parsing my sql statement to JSON format, like:
{"CREATE":{'table_name':'TABLE_B', 'SELECT': {'columns': '*', 'FROM': {'SELECT':'columns':'*','FROM':['TABLE_A','TABLE_B'],'WHERE':{'SELECT': .... }}}}
Something like this for me access all statements and ignore the ones is not useful for me, like the tables accessed in WHERE statement.
I already have tried some libraries like sql_metadata, but it returns all the tables and i can't know from which statement it came from.
Can you help me?