January 21, 2014 at 4:09 am
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!!
January 21, 2014 at 5:22 am
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" 😉
January 21, 2014 at 5:31 am
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
January 21, 2014 at 5:33 am
I need to run it every fortnight. Today I am doing the same and almost one day it takes....
January 21, 2014 at 5:38 am
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
January 21, 2014 at 5:52 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2014 at 5:59 am
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
January 21, 2014 at 1:09 pm
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
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply