SQL Clone
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
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52911 Visits: 17672
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34005 Visits: 16649
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
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34005 Visits: 16649
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
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96887 Visits: 33013
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34005 Visits: 16649
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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24158 Visits: 13698
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