Derive table names from queries used in workflow

[Agarwal_Yash91]

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

[Agarwal_Yash91]

Hi @atcodedog05 another example

[atcodedog05]

Hi @Agarwal_Yash91 

There is no dot between schema name and table name is that allowed in SQL query 🤔

[Agarwal_Yash91]

@atcodedog05 probably i made a miss while trying to mask data.

we have a dot between schema name and table name

[atcodedog05]

Hi @Agarwal_Yash91 

Here is how you can do it. I am taking an assumption that alias name is always 1 char length.

Workflow:

Hope this helps : )

[atcodedog05 · Accepted solution]

Hi @Agarwal_Yash91 

If you have list of schemas you can do something like this. Which would be much more efficient.

Workflow:

Hope this helps : )

[Agarwal_Yash91]

@atcodedog05 this worked like wonder..

thanks..

[atcodedog05]

Happy to help : ) @Agarwal_Yash91 

This was an interesting project 🙂

Cheers and have a nice day!

[DataDynamite]

Stumbled upon this old post while trying to solve another issue.

If you are able to query objects within the database this problem can be solved without parsing the SQL code.

My approach would be this: