Transpose every table in database

Hi all,

I'm hoping to transpose every table in a SQL Server database into three columns: table name, field name, & field value. My end goal is to cross join the result set to itself on the field values (with grouping and summarization to reduce the size of the data set) in order to determine the match percentages between fields, thus informing other users how best to join tables.

I understand how to do the cross join and summarization, but setting up a batch macro to transpose each table into a unified result set has flummoxed me. My thought was to use the INFORMATION_SCHEMA.TABLES view to retrieve a list of table names to use as a control parameter, but I don't understand how to use that list to change the data input. Perhaps I'm on the completely wrong track. See attached.

Thank you in advance,

Tim

Macros

Transformation

Accepted answers

rdoptis

Hi Tim,

My example has metadata within it from my testing which is confidential so I am unable to post the module, but I will explain further.

There are different kinds of macro inputs - namely data inputs and control parameter inputs. With a data input (macro input) you have to define a template. With parameter (control parameter) inputs there is no template - these can be thought of as variables to inject in the macro configuration. Batch macros in general often leverage control parameters to alter what should modified between run in a batch. In our case the table name is what we want to change for each run within the batch. For each record being fed into the control parameter, it will run an loop of the batch macro.

From the control parameter, you would use an Action Tool to modify another tool's configuration with the control parameter. The action tool configuration should look similar to this:

In this case, the action tool is doing something of a "find and replace" of the string "TABLENAME" within the query, and subbing in each of the table names.

Does this help clear things up?

Best regards,

Ryan

All comments

rdoptis

Hi Tim,

You would use a combination of an input tool and batch macro for this. The rough order of operations would be:

1) Use standard input tool to query the "tables" table in your DB to get a list of the table names.

2) Feed this list of table names into a batch macro that looks like:

Batch Crosstab Tables.png

I setup the query in the input tool with a select distinct * so the database would dedupe your data and shrink the amount of data brought back to Alteryx.

3) In the interface settings for the macro, set the following as your data values will be of different types:

4) Place this macro in your workflow like:

This should produce the data you want, but be warned, it will create some amazingly tall tables. You will have a row for every {Column domain count * column count * table}. Let me know if you have any other questions.

Thanks,

Ryan

PS. As soon as I saw your workflow name containing Alpo, I thought of Tableau's internal instance ;-)

thughes

Hi Ryan,

Thanks for your speedy response! Your workflow makes a lot of sense; it's quite similar to the stored procedures I was writing (until I ran out of hardware to process them).

This is my first time using macros in Alteryx and I'm still struggling to figure out exactly how updated values are received into the SQL query. My understanding is that the macro uses a data input as a template, and in order to build the rest of the macro said template either has to be a text input or an actual connection (i.e., not a placeholder query). So then how do I use the action to pass new table names to the table in the FROM clause of the SQL query?

Would you be willing to post your workflow file so I can a look at the nuances?

Thanks so much for your help,

Tim

P.S. The use case for this is in fact Tableau's internal SQL Server database

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703