Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run Script in each databases in sql server Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 21, 2014 5:36 AM
Points: 91, Visits: 164
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!!

Post #1532954
Posted Tuesday, January 21, 2014 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 5,970, Visits: 12,868
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"
Post #1532978
Posted Tuesday, January 21, 2014 5:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,077, Visits: 8,919
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
Post #1532980
Posted Tuesday, January 21, 2014 5:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 21, 2014 5:36 AM
Points: 91, Visits: 164
I need to run it every fortnight. Today I am doing the same and almost one day it takes....
Post #1532982
Posted Tuesday, January 21, 2014 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,077, Visits: 8,919
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
Post #1532986
Posted Tuesday, January 21, 2014 5:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 14,836, Visits: 27,312
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532992
Posted Tuesday, January 21, 2014 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,077, Visits: 8,919
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
Post #1532999
Posted Tuesday, January 21, 2014 1:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 5,850, Visits: 12,596
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


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

Post #1533323
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse