Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

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

Sorry, I don't understand what you mean.

vishnu_2920
7 - Meteor

Hi @Aguisande 

 

I really don't have any idea to create batch macro's . Now i have list of database names now how to get list of table names using batch macro.

Aguisande
15 - Aurora
15 - Aurora

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.

Labels
Top Solution Authors