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

[AkisM]

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. 

[Aguisande]

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]

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]

No problem, let me work something for you.

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

EDIT1: Please, see attached package.

[Aguisande]

Re uploading

[AkisM]

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 · Accepted solution]

can you try using the attached package?

[vishnu_2920]

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]

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]

Hi @Aguisande 

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

Thanks

vishnu Teja.K