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
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.
As usuall, Debbie is right on taget.
Just this week I created an automation script that would save all Reference tables to an Excel Sheet, with each table writing to its own sheet.
I added a loop connection activity and for each connection it created a seperate File. I did this by using the connection variable in the sheet name.
I wanted to add seperate branches for different categories and found that the connection didn't get passed to the child activity. Still it worked like a charm.
I did enter each table name seperately, but used a variable to store / change the schema name.
It took me all of 2 hrs and now I have a backup before I start any mass updates.
Shimon