Verify if a Column of a particular SQL Table is present or not from a given Select SQL Query(Oracle) , using Python and no access to Database

Question

Given a SQL query(Oracle) I want to find if column of a particular table is present in it or not. I want to achieve this using Python. I do not have any access to the Database and I needed this for analysis purpose.

I have thought about using Regular expressions but I think it might cause errors and there is a possibility of giving wrong results from complex SQL queries

so I have tried using sqlglot(sqlparser) library and was able to extract columns and table names separately. But I wanted to find columns as in Table_name.Columns and should be able to find even though the table name or column name get aliased.

What would be the most efficient way to achieve this?

Answer 1

In general, this is not possible without access to the database.

It is perfectly legal to write SQL statements where some columns don't have aliases

SELECT col1, col2, col3
  FROM parent p
       join child c on (c.parent_id = p.parent_id)
 WHERE p.parent_id = 12345;

Without access to the database, it is impossible to determine whether col1, col2, and col3 are coming from the parent table or the child table (or both in which case a syntax error would be thrown).

You get similar issues when you have something like

SELECT a.b( 123 ) 

Where a could be a schema name and b a function in that schema or a could be a package name and b a function within that package (and this only gets more complicated if you're taking advantage of your database and using object types and things like that that create more possible interpretations).