SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Execute SQL script on multiple databases of one server

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Execute SQL script on multiple databases of one server
Author Message
jtraser



Joined: 30 Jul 2012
Posts: 3
Country: Germany

Post Execute SQL script on multiple databases of one server Reply with quote
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
Mon Jul 30, 2012 9:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Mon Jul 30, 2012 12:14 pm View user's profile Send private message
jtraser



Joined: 30 Jul 2012
Posts: 3
Country: Germany

Post Bulk Data Generator - select all Databases Reply with quote
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
Tue Jul 31, 2012 1:49 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.