Hi,
we are trying to get the list of various tables used in SQL queries as input for alteryx workflows.
we have derived queries using xml parse and now trying to get list of tables present in input query.
List of Schema names are available in separate sheet.
Example:
Input 1:
| Source System | Schema Name |
| PROD | Schema_1 |
| PROD | Schema_2 |
| PROD | Schema_3 |
Input 2:
| Tool ID | Table query |
| 1 | select *A. name ,B.id from schema_1.table1 A join schema_1.table2 B on a.no=b.no |
| 2 | select *A. name ,B.id from schema_2.table1 A1 join schema_2.table2 B1 on a.no=b.no |
| 3 | select *A. name ,B.id from schema_3.table1 A2 join schema_1.table2 B2 on a.no=b.no |
| 4 | select *A. name ,B.id from schema_1.table3 A3 join schema_1.table2 B on a.no=b.no |
Expected Output:
| Tool | Tables in Use |
| 1 | schema_1.table1,schema_1.table2 |
| 2 | schema_2.table1 , schema_2.table2 |
| 3 | schema_3.table1 ,schema_1.table2 |
| 4 | schema_1.table3 ,schema_1.table2 |
Any idea?
Thanks
