Toad World® Forums

Automation looping and exporting


#1

I’m new to Toad Automation.

Would Toad be able to run this code without any interaction with me?

for each connection in list of connections
for each schema in list of schemas
for each table in list of tables
export to excel the list of field names, naming the excel file “connection”
“schema” “table”.xlsx
next
next
next

Thanks


#2

You can get close to what you want by using the Multi DB Query Facility
Available from Tools - Query Multiple Databases (if you have a Toad DB2 DBA License)
Select the connections you wish and use this Query:
SELECT
TABSCHEMA, TABNAME, COLNAME, COLNO, TYPESCHEMA, TYPENAME, LENGTH, SCALE, NULLS
FROM SYSCAT.COLUMNS
where TABSCHEMA not like ‘SYS%’
order by TABSCHEMA, TABNAME, COLNO

I am not an expert at automation, but I asked our in-house expert Debbie and this was her response:

I haven’t tried this but I would suggest this:

Loop connections activity;
– Loop Dataset – Driving query would list tables in a particular schema
----- Child select to file – using variable from current row of loop dataset, select all columns for that table and export to excel appending in a datasheet.

I believe there are some fixes in automation associated with passing connections to other child activities.
Those have been addressed and will be in our next release (target to be out by end of April).

Hope this helps.