I would like to automate a group execute script but I don't think this is a current feature. I have read that the workaround is to use the Loop Connections tool. However, I can't see how to specify the individual databases to loop through. I have ~50 databases within a single SQL server instance and would like to loop through the individual databases rather than connections. Can this be done? Thanks, James.
Can be done... probably best via Automation flows.. Here's possibilities to consider...
-
Using Toad Automation,
you can loop through a connection list for your 50 databases and run the same script for each of those DBs. This method might require you to create a separate connector for each of your databases, even though they may be on the same server. Snap 1 below shows a quick automation flow sample. -
Using Toad Automation,
but instead of using the Loop Connections task, use the Loop Dataset task. You can loop through a file or table that has a list of your database/schema names, and set a variable to this name on each pass of the loop and execute a script that uses the variable as a prefix to designate the correct objects. See Snap 2 below for a template.
Hope this gives you some ideas to play with.
Toad Automation using Connection Looping:
Toad Automation using Dataset Looping:
Thanks for pointing me in the right direction Gary.
Option #1 - you're right, I found out that I need to create 50 connections to the same server (one for each database) and loop through all of these.
Option #2 - I hadn't thought about using a variable as an object prefix, I will give this a try!
Thanks!
Good to hear... probably depends on how familiar you are with using TDP variables, but I think in your case, option #2 (looping through a table/dataset of database names and assigning to variable) gives you extra flexibility...
e.g. in case your boss wants you to run your script on 20 more databases, you just need to add those to your driving file. 