Execute SQL script on multiple databases of one server

Hello,

i try to execute a SQL script on 200 different databases of one server.
Do i have to put in 200 DB connections in the DB Options or ist there an easier way to do this?

Best regards
Joerg

If the script is fairly simple, it can be executed in a loop as a dynamic SQL string like

EXEC sp_MSforeachdb
@command1='ALTER DATABASE ? SET PAGE_VERIFY CHECKSUM' -- SQL 2005 Best Practice

But if your script is complex and it won't work as a dynamic string, you would need to define a connection for each database and use Execute SQL Script (Multiple Servers) command in SQL Assistant menu, then select each required connection.

But since the server is the same for all of them, you are lucky and can use the following trick to get it all done with a few mouse clicks using Bulk SQL Data Generator (you must be running version 6.2, this function is not available in earlier releases)

1. In SQL Assistant options click Code Generator tab
2. In the left -button box, right click and choose to add new Template, name it for example, My Script
3. On the right side, paste your script and save all changes, close Options dialog.
4. In the editor, right-click and from context SQL Assistant's menu choose Bulk SQL Data Generator, choose My Script template and select the databases you want to run the script on.
Tip: to quickly select all databases, use the Menu button above the database tree view.

Hope this helps.

Hello,

thanks for that info. I entered a new Template with "$CURRENT_SEL$" to run the selected text on all Databases.
It works fine.
But it would be nice to have an entry in the menu button like "select all databases on Server".
We have about 200 databases on one server and single selection of all databases is really uncomfortable.

Best Regards
Joerg