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

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.

Workflow

Accepted answers

Aguisande

can you try using the attached package?

ReadTables.yxzp

All comments

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.

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

Aguisande

Sorry, I don't understand what you mean.

Aguisande

Got you.

Maybe you can start here: Getting Started with Batch Macros - Alteryx Community

Batch macros differ from Standard macros, bacause they can take Control Parameters (You can use these control Parameters to pass [DB]+[Table] to a macro for instance).

The logic within the macro will run for each record in the Batch macro Control Parameter data.

Let me know if this helps or you need further assistance.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703