Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

AkisM
10 - Fireball

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. 

12 REPLIES 12
Aguisande
15 - Aurora
15 - Aurora

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

AkisM
10 - Fireball

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.

Aguisande
15 - Aurora
15 - Aurora

No problem, let me work something for you.

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

 

EDIT1: Please, see attached package.

Aguisande
15 - Aurora
15 - Aurora

Re uploading

AkisM
10 - Fireball

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?

Aguisande
15 - Aurora
15 - Aurora

can you try using the attached package?

 

vishnu_2920
7 - Meteor

Hi @Aguisande ,

 

There are a multiple databases in the server. I would like to read only two specific columns from each tables.

Would you suggest using the the above package for my requirement. If so what type of macro you used in the workflow?

 

 

Aguisande
15 - Aurora
15 - Aurora

Hi @vishnu_2920 

For what I understand you need to query two fields from all the tables within several databases, right?

I'm afraid the packageg "as is" won't work at all.

 

What you can use from the previous package is the process logic.

- Query the master.dbo.sysdatabases to get all DBs in that server.

- With the list of databases, you can have a BATCH Macro (That's the macro type I'm using), that receives the DB name and queries for all its TABLE names.

- With the Table names for each DB, have another Batch macro to query the fields you need from each TABLE (The actual data you'll ouput).

 

vishnu_2920
7 - Meteor

Hi @Aguisande 

 

could you please the macro that you have, it will helpful for me.

 

Thanks

vishnu Teja.K

Labels