Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run Script in each databases in sql server


Run Script in each databases in sql server

Author
Message
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 226
Hi All,

I have a Script which just insert data into two tables (bankoffice1 and Bankoffice2). these 2 are tables are independent tables.

Now I need to ran this Script into all my databases. Currently I have more than 100 databases in my production server. Currently I am going to each database then run the script one by one.

Is there any way so that I ran the script in all databases running the SP like sp_msforeachdb? and also it should check whether the database has the tables (bankoffice1 and Bankoffice2) or not.

If the tables are present then it will insert the data otherwise not.

Please help!!
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8855 Visits: 16590
If you have a central management server configured you could group execute the query there across pre defined instances

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7520 Visits: 15175
Why do you say "like sp_msforeachdb"? Is there any reason not to use that stored procedure? Is this a one-off, or a regular requirement?

John
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 226
I need to run it every fortnight. Today I am doing the same and almost one day it takes....
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7520 Visits: 15175
If you need to run it regularly then it's worth taking the time to script it properly. Are the databases that contain the table the same databases from week to week?

John
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17647 Visits: 32271
Just use sp_msforeachdb. That's what it's for. But, if you really, really don't want to use it, set up a cursor to cycle through the databases (that's all that query is anyway). If you want to get really fancy though, you could use Powershell, set up multi-threading and then pipe the command to a listing of databases. You can get simultaneous execution that way. But that's not that easy to program.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7520 Visits: 15175
But you don't want it running against databases that don't have the tables - that would be a waste of resources. That's why I suggested scripting it properly. Of course, if databases are being added or removed all the time, that would make it more difficult.

sp_MSforeachdb is an undocumented stored procedure, so I wouldn't use it for something that will run regularly in production. I know that some take the view that it's been unchanged for many years now and is therefore fairly safe. You'll make your own mind up about that.

John
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6364 Visits: 13687
John Mitchell-245523 (1/21/2014)
But you don't want it running against databases that don't have the tables - that would be a waste of resources. That's why I suggested scripting it properly. Of course, if databases are being added or removed all the time, that would make it more difficult.

sp_MSforeachdb is an undocumented stored procedure, so I wouldn't use it for something that will run regularly in production. I know that some take the view that it's been unchanged for many years now and is therefore fairly safe. You'll make your own mind up about that.

John


+1

use a cursor to loop through. If this all user databases you can easily build up a list of them in the cursor FOR clause with 'select name from master.sys.databases where database_id > 4'. That way its flexible and automatically copes with databases being dropped or added. You can also exclude\include based on the database status if say you have offline or read-only databases.

Complex logic is much easier with cursors than that yukky sp_msforeachdb

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search