Python-Parsing a SQL using pyparsing

Question

I want to parse a complex SQL which has (inner join,outer join) and get the table names used in the SQL.

I am able to get the table names if it is simple select but if the SQL has inner join ,left join like below then the result is giving only the first table.

select * from xyz  inner join dhf  on df = hfj  where z > 100 

I am using the program similar what is present in the below link by Paul.

http://pyparsing.wikispaces.com/file/view/select_parser.py/158651233/select_parser.py

Can someone tell me how to get all the tables used in a SQL like below

select * from xyz  inner join dhf  on df = hfj  where z > 100.  

Answer 1

Your question is going to depend on what Sql platform you are using.

I will answer assuming you are using MsSql. The same logic should be able to be done on all Sql platforms thought the syntax changes though.

Tables are unique by a combination of Owner and Table. I do a select that returns #Owner#TableName# in a Python script that I wrote to extract all data in all tables to text files. The basic form of this assuming you do not have multiple tables of the same name with a different owner is:

Select name from SysObjects where xtype = 'U' order by name

This gives you a list of all tables. Then you take that list and do a "Select * from [table name from other query]" looping through till you have all the tables that you found when you selected from Sysobjects.

Same type of thing is practical on all Sql Platforms assuming you have access to the system tables.

Answer 2

This parser was written a long time ago, and handling multiple values in a results name did not come along until later.

Change this line in the parser you cited:

single_source = ( (Group(database_name("database") + "." + table_name("table")) | table_name("table")) + 

to

single_source = ( (Group(database_name("database") + "." + table_name("table*")) | table_name("table*")) + 

When I run your sample statement thru the select_stmt parser, I now get this:

select * from xyz  inner join dhf  on df = hfj  where z > 100
['SELECT', ['*'], 'FROM', 'xyz', 'INNER', 'JOIN', 'dhf', 'ON', ['df', '=', 'hfj'], 'WHERE', ['z', '>', '100']]
- columns: ['*']
- table: [['xyz'], ['dhf']]
  [0]:
    ['xyz']
  [1]:
    ['dhf']
- where_expr: ['z', '>', '100']