Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Read 200 tables from SQL server without specifying each table separately in SQL query?

Highlighted
8 - Asteroid

So with the basic good old input tool I am connecting to an SQL database on my company's server which contains like 200 different tables. Is there a way I can dynamically update the SQL query to pull let's say just the 1st row of every single table in that database and save each table to a different excel tab named after that table? It goes without saying that each table has a different structure (number of columns, etc)

 

Or, alternatively, an SQL query that will give me the list of all tables in a database, including a list of the column names of each table? I understand that:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

will give me the list of tables, but it doesn't have the column names of each table as well. If I could get the column names as well in a 2nd column that'd be perfect for me. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @AkisM 

the Dynamic Input tool may help you with this requirement.

Take a look at it: https://help.alteryx.com/2020.2/DynamicInput.htm

Hope this helps

Highlighted
8 - Asteroid

Hi @aguisande ,

That's what I thought too, however my experience with alteryx isn't at a high enough level yet to be able to build what I described above with the dynamic tool, which is why I was hoping for something a little more specific.

 

I did manage to get a list of all tables/column names using a standard SQL query that reads from information_schema.columns

 

But i'd still like to learn how to do what i described in the first part of the question - read a large number of tables with different schema from a database without specifying all of them in a tailor-made sql query.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

No problem, let me work something for you.

I'll be back in a minute, stay tuned...

 

EDIT1: Please, see attached package.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Re uploading

Highlighted
8 - Asteroid

Appreciate your time @aguisande , unfortunately I can't open that file on my Alteryx due to my alteryx being a bit older (2019.3.5.17947). And updating is not an option as we only update when the company's server is upgraded.

 

Which leads me to my next semi-related question, if we've got installed Alteryx server 2019.3 but on my pc i have let's say alteryx designer 2020.4.x.xxxxx are there gonna be compatibility issues when trying to save to and read from the server gallery?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

can you try using the attached package?

 

Labels