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

[mceleavey]

Hi @Agarwal_Yash91 ,

I've done this by the cunning use of Regex...

As I can only assume your text string are representative of the real data, I have built the regex to take everything from the word "from" to the first instance of a space and an uppercase letter:

(?<=from)(.*)(?=\s\u)

This gives the following result:

Workflow attached.

Hope this helps,

M.

[atcodedog05]

Hi @Agarwal_Yash91 

Here is my take. I went a step ahead in Regex 😜

Workflow:

Hope this helps : )

[Agarwal_Yash91]

Hi @atcodedog05  is not necessary that Schema name will start or will contain word Schema. schema details are available in different input sheet, i hope it makes some sense.

[Agarwal_Yash91]

Hi @mceleavey queries can get complicated with lot of sub queries and getting data in this manner will not help.

[atcodedog05]

Hi @Agarwal_Yash91 

Here is how you can do it. I am using tokenize to extract schemaname.tablename

Workflow:

Hope this helps : )

[Agarwal_Yash91]

Hi @atcodedog05 this is not working, getting column names present in select statement 

[atcodedog05]

Hi @Agarwal_Yash91 

Please provide more variance of sample data so that we can look into it.

[Agarwal_Yash91]

Hi @atcodedog05 ,

Due to sensitivity of data, I am not able to provide lot of data but somehow managed to mask some of the queries which are not giving desired output.

PFA.

[atcodedog05]

Hi @Agarwal_Yash91 

Here is how you can do it.

Workflow:

Hope this helps : )