Create Table Names of SQL DB Input

Question

I have an input tool connection to a third party SQL database. It contains roughly 100 tables with various fields unique to each. I'm trying to connect to the database then create a list of the table names and fields to generate a sort of data dictionary. The third party adds tables and fields regularly, so I need it to be dynamic and pick up the updates vs a excel file they send me once in a blue moon.

What tool, or combination of tools would I use to generate a list of tables and fields in each table?

WirkKarl · Answer

I had to do something very similar before, and yeah — keeping an Excel file updated is basically impossible when things change that often.

What worked for me was just pulling metadata directly from the database (like the system tables that store info about tables and columns). Once you bring that into Alteryx, you can shape it into a clean data dictionary pretty easily. The nice part is it updates automatically whenever new tables/fields are added.

You can start simple with a custom SQL query in the Input tool, then clean it up with a few basic tools. If you want to get fancy later, you can turn it into a macro.

I’ve seen the same idea used in platforms like phonexa too — way better to rely on live data structure instead of static files that go out of date.

jrlindem · Answer

I agree with @abe_ibanez in that using the information schemas, different for each type of database, is a good route to take.  When I mean different databases, I mean Oracle vs MS-SQL vs Snowflake vs _________.  It will also depend on if you have access to read from that schema.  Most roles/accounts do, but your admins would be the ones to help you there if not.

Then you just query from it as you would any other database object (table or view).  -Jay

aatalai · Answer

also take at the dynamic stream in tool ( you might also need to buld a batch macro)